Home 
Search 
Today's Posts 
#1




Counting items in one column based on criteria in another column
I am looking to calculate the total number of students enrolled in each class
on each day the class is offered: Column A lists the student's name Column B lists the class the student is taking Column C lists the date the class will be taken So, I want to find out how many students are taking class X on Monday, then how many are taking it on Tuesday, etc., for each class Y and Z and so on. As a formula I was thinking along the lines of: Count the number of times X appears in Column B if the date in Column C is Monday I've tried using IF and COUNTIF, but can't seem to get the syntax to work, so I'm thinking I'm going about it all wrong. Any suggestions? It would be most appreciated! Thank you! 
#2




Counting items in one column based on criteria in another column
One way:
=SUMPRODUCT((B2:B100="X"),(WEEKDAY(C2:C100)=2)) HTH, Paul  "luttona" wrote in message ... I am looking to calculate the total number of students enrolled in each class on each day the class is offered: Column A lists the student's name Column B lists the class the student is taking Column C lists the date the class will be taken So, I want to find out how many students are taking class X on Monday, then how many are taking it on Tuesday, etc., for each class Y and Z and so on. As a formula I was thinking along the lines of: Count the number of times X appears in Column B if the date in Column C is Monday I've tried using IF and COUNTIF, but can't seem to get the syntax to work, so I'm thinking I'm going about it all wrong. Any suggestions? It would be most appreciated! Thank you! 
#3




Counting items in one column based on criteria in another column
In pre2007 versions:
COUNTIF allows only one criterion =SUMPRODUCT((B1:B100="X"),(C1:C100="Monday")) The double negation  converts Boolean FALSE/TRUE into numbers 0/1 SUMPROIDUCT (pre XL2007) does not work with full columns so do not use B:B and C:C but use an actual range For more details, Google with "SUMPRODUCT Excel" In XL2007  read Help on COUNTIFS which allows many criteria best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "luttona" wrote in message ... I am looking to calculate the total number of students enrolled in each class on each day the class is offered: Column A lists the student's name Column B lists the class the student is taking Column C lists the date the class will be taken So, I want to find out how many students are taking class X on Monday, then how many are taking it on Tuesday, etc., for each class Y and Z and so on. As a formula I was thinking along the lines of: Count the number of times X appears in Column B if the date in Column C is Monday I've tried using IF and COUNTIF, but can't seem to get the syntax to work, so I'm thinking I'm going about it all wrong. Any suggestions? It would be most appreciated! Thank you! 
#4




Counting items in one column based on criteria in another colu
Thank you! This solved the problem brilliantly!
"Bernard Liengme" wrote: In pre2007 versions: COUNTIF allows only one criterion =SUMPRODUCT((B1:B100="X"),(C1:C100="Monday")) The double negation  converts Boolean FALSE/TRUE into numbers 0/1 SUMPROIDUCT (pre XL2007) does not work with full columns so do not use B:B and C:C but use an actual range For more details, Google with "SUMPRODUCT Excel" In XL2007  read Help on COUNTIFS which allows many criteria best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "luttona" wrote in message ... I am looking to calculate the total number of students enrolled in each class on each day the class is offered: Column A lists the student's name Column B lists the class the student is taking Column C lists the date the class will be taken So, I want to find out how many students are taking class X on Monday, then how many are taking it on Tuesday, etc., for each class Y and Z and so on. As a formula I was thinking along the lines of: Count the number of times X appears in Column B if the date in Column C is Monday I've tried using IF and COUNTIF, but can't seem to get the syntax to work, so I'm thinking I'm going about it all wrong. Any suggestions? It would be most appreciated! Thank you! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Counting items in one column based on criteria in another  Excel Worksheet Functions  
Counting entries in column based on condition in another column  Excel Worksheet Functions  
sum items in a column based on criteria located in other columns  Excel Discussion (Misc queries)  
move contents of column C based on criteria related to column A  Excel Discussion (Misc queries)  
Counting & Summing based on criteria on another column  Excel Discussion (Misc queries) 