how can i sum if certain dates meets criteia?

[Kati]

I am using Excel to summarise data and i need to count a the number of times
certain criteria appear. The first criteria is if one cell equals a course
name and the second criteria is if one of 5 dates appear in that cell range!
Does anybody know how i can do this using a countif function or any other
function???
I have tried the function below but it has returned an error.
=COUNT(IF((Candidates!H2:H200="Pre-level 2
learners")+((Candidates!I2:I200="02/08/2004")*OR(Candidates!I2:I200="03/08/2004")*OR(Candidates!I2:I200="04/08/2004")*OR(Candidates!I2:I200="05/08/2004")*OR(Candidates!I2:I200="06/08/2004")),1,0))

 

[Govind]

Hi,

Try using sumproduct.

=SUMPRODUCT((Candidates!H2:H200="Pre-level 2
learners")*((Candidates!I2:I200="02/08/2004")+(Candidates!I2:I200="03/08/2004")+
(Candidates!I2:I200="04/08/2004")+(Candidates!I2:I200="05/08/2004")+(Candidates!I2:I200="06/08/2004")))

Regards

Govind

 

[Kati]

Hi, that has not worked, it has returned zero and i know the answer shold be
three. Do you have any other ideas?
kati

 

[Govind]

Hi,

Sorry about that.

Try

=SUMPRODUCT((Candidates!H2:H200="Pre-level 2
learners")*(Candidates!I2:I200=DATE(2004,8,2))+(Candidates!I2:I200=DATE(2004,8,3))+(Candidates!I2:I200=DATE(2004,8,4))+(Candidates!I2:I200=DATE(2004,8,5))+(Candidates!I2:I200=DATE(2004,8,6)))

Regards

Govind.

 

[Kati]

Hi, just another question! Should i not be using OR between each date
statement as i dont want to sum if all of the dates are shown just if one of
them appears?
kati

 

[mangesh_yadav]

Hi Kati,

Govind's solution works nicely with me...! Please check if you misse
out on any parenthesis.

- Manges

 

[Kati]

Hi, i have checked and i have not missed out any parenthesis, i am not sure
what is wrong but it keeps giving me the answer 4 when it should be three.
The answer 4 comes from the number of pre-level 2 learners so i dont think it
is taking into acocunt the dates as there are only 3 pre-level 2 learners in
those dates. Are you sure it is working correctly for you?
thanks, kati

 

[mangesh_yadav]

Govind has missed out on one set of parenthesis himself. Use this:

=SUMPRODUCT((Candidates!H2:H200="Pre-level 2
learners
)*((Candidates!I2:I200=DATE(2004,8,2))+(Candidates!I2:I200=DATE(2004,8,3))+(Candidates!I2:I200=DATE(2004,8,4))+(Candidates!I2:I200=DATE(2004,8,5))+(Candidates!I2:I200=DATE(2004,8,6))))


- Manges

 

[Govind]

Hi Mangesh,

Thanks for the correction.

Govind.