count if one column
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 |
count if one column
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 |
count if one column
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(--(((D1:D19="No")+(E1:E19="No"))*(B1:B19="annex")1) ) -- Thanks, Shane Devenshire "Kristamarie" wrote: 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 |
count if one column
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. -- Krista "ShaneDevenshire" wrote: 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(--(((D1:D19="No")+(E1:E19="No"))*(B1:B19="annex")1) ) -- Thanks, Shane Devenshire "Kristamarie" wrote: 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 |
All times are GMT +1. The time now is 12:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com