Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,"=")

share|improve this question
Edited my answer to offer a useful solution (not involving OR, but working nonetheless). – kopischke Oct 30 '11 at 15:41
up vote 4 down vote accepted

If you are thinking of ORing 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"},

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).

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.