| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
| Count entries in one column based on values in another column | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. | Excel Worksheet Functions | |||
Linear Mode
