Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 3 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).

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
add comment

Your Answer

 
discard

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.