# How do I use an OR clause in a COUNTIFS formula?

This formula works, but it'd be simpler with an OR statement in there somewhere...

=COUNTIFS('Sheet1'!\$J:\$J,"=HNT",'Sheet1'!\$A:\$A,"="&Q\$4,'Sheet1'!\$AA:\$AA,"<"&\$A\$2-2,'Sheet1'!\$AC:\$AC,"=")+COUNTIFS('Sheet1'!\$J:\$J,"=HNT Fuel",'Sheet1'!\$A:\$A,"="&Q\$4,'Sheet1'!\$AA:\$AA,"<"&\$A\$2-2,'Sheet1'!\$AC:\$AC,"=")

-
Edited my answer to offer a useful solution (not involving `OR`, but working nonetheless). – kopischke Oct 30 '11 at 15:41

If you are thinking of `OR`ing the two criteria “HNT” and “HNT Fuel”, I am very much afraid that is not possible: `OR` doesn’t return anything of use to `COUNTIFS`. However, the `COUNTIF` and `SUMIF` formulas will accept an array of criteria, outputting an array of results, which you can sum up.

The following formula gets rid of the repetition and is extensible to allow for more variants:

``````=SUM(COUNTIFS('Sheet1'!\$J:\$J,{"HNT","HNT Fuel"},
'Sheet1'!\$A:\$A,"="&Q\$4,
'Sheet1'!\$AA:\$AA,"<"&\$A\$2-2,
'Sheet1'!\$AC:\$AC,"="))
``````

Note you do not need the `=` when comparing a string literal for equality (there are gotchas as to cell data to string conversion when you use a cell reference, but not with literals).

-
Edited after stumbling over Colin Legg’s superb `SUMIF` Workshop. – kopischke Oct 30 '11 at 15:40
This works well - thanks! – Dave Nov 1 '11 at 19:16