Counting items in one column based on criteria in another column

[luttona]

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!

 

[PCLIVE]

One way:

=SUMPRODUCT(--(B2:B100="X"),--(WEEKDAY(C2:C100)=2))

HTH,
Paul

 

[Bernard Liengme]

In pre-2007 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

 

[luttona]

Thank you! This solved the problem brilliantly!

In pre-2007 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