To count "NO" in column D when corresponding B is "Annex"
=SUMPRODUCT(--(B1:B100="Annex"),--(D1:D100="NO"))
To count when B is "Annex" and either C or D is "NO".
=SUMPRODUCT(--(B1:B100="Annex"),(D1:D100="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"),(D1:D100="NO")*(E1:E100="NO"))
Do not use full column references with SUMPRODUCT
=SUMPRODUCT(--(B:B="Annex"),(D: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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Kristamarie" wrote in message
...
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
--
Krista