up vote 0 down vote favorite
share [g+] share [fb]

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

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

1 Answer

up vote 2 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"},
              '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).

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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