Count-If function with multiple variables

[Ron]

I have a database that includes names of people who have
registered for 10 different courses and the date that they
registered. I would like to find a formula that counts
the number of people who have registered for each course
by month. (i.e. I would like it to search the database
range for people who have registered for course #1 between
01/01/04 and 1/31/04).

I know how to do the countif function but don't know how
to add the second variable to the formula. Any thoughts?

 

[Don Guillett]

try
=sumproduct((year(a2:a200)=2004)*(month(a2:a200)=1)*(b2:b200=1))

 

[Dave Smith]

You should be able to do this with SUMPRODUCT().

 

[Ron]

I'm not getting it. I believe that function will give me
the count of people who registered in that month but won't
distinguish between the different types of courses. I
need it to look for month and course.

 

[Dave Smith]

If courses are in column B then this will only count columns that are course
1 ( notice the "b2:b200=1")

 

[Don Guillett]

Since you didn't mention how the data is laid out, I assumed a date in col A
and a course number in col B such as 1,2,3,4,5,6
12-Jan 1
13-Jan 2
14-Jan 1

 

[Guest]

Thanks for clarifying. I'm still having trouble making
this work. I'm getting an #N/A. I don't know if this
matters but the courses are not numbers, they are text.
Also the relevent columns are not next to each other, i.e.
conference name is column B and the date is in column T.

Any other thoughts?

 

[Don Guillett]

Can't you figure out what to do from the example given? Change the
references to suit your needs and the course number from 1 to course #1 or
whatever it is..... If you still can't get it, send me (at the address
below) a SMALL workbook sample.