count if one column

[Kristamarie]

I am brain dead...I cannot remember how to count value in one column if
another coulmn meets a certain criteria.
For instance
I want to count all the "NO" in columns D & E, but only if column B is Annex

Heather Annex Business Services NO NO
Enedina Stock Storekeeper 1 NO
Thomas JCW Operator 0 1
Arnold Fuel Fuel Handling 0 0
David K12 Maintenance 0 NO
Eric K12 Operator 0 0
Gary K12 Maintenance 0 NO
Tammy K12 Maintenance 0 NO
Chris JCW Maintenance 1 NO
Frank K34 Maintenance 1 NO
Craig Fuel Fuel Handling 0 1
Kenneth JCW Technical Business 0 NO
Alfredo JCW Operator 0 0
Michael JCW Maintenance 0 NO
Kurt K12 Operator 1 1
Tara Stock 0 NO
Mark Fuel Fuel Handling 0 0
Lukus Fuel Fuel Handling 0 1
Marty Annex Business Managers 0 0

 

[Bernard Liengme]

To count "NO" in column D when corresponding B is "Annex"
=SUMPRODUCT(--(B1:B100="Annex"),--(D1100="NO"))

To count when B is "Annex" and either C or D is "NO".
=SUMPRODUCT(--(B1:B100="Annex"),(D1100="NO")+(E1:E100="NO"))
Of course, if both D and E are "NO" this counts for 2

To count when B is "Annex" and both C and D is "NO".
=SUMPRODUCT(--(B1:B100="Annex"),(D1100="NO")*(E1:E100="NO"))

Do not use full column references with SUMPRODUCT
=SUMPRODUCT(--(B:B="Annex"),(D="NO")*(E:E="NO"))
unless you are in XL2007

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[ShaneDevenshire]

Hi,

Based on your data I would ask one question - if a row has the word Annex
and has No in column D and E do you count that as 1 or 2 No's?

If two, than the formula would be like Bernards:
=SUMPRODUCT((D1:E19="No")*(B1:B19="Annex"))
If once, then the formula would be:
=SUMPRODUCT(--(((D119="No")+(E1:E19="No"))*(B1:B19="annex")>1))

 

[Kristamarie]

Thank you Bernard and Shane. Yes, I do want it to count how many "NO"'s.
That way we know how many classes they have not completed. I appreciate your
help...sooooo much.