CountIF problemo

[David0079]

Hi Ladies & Gents,

Little problem for Friday afternoon!

I have three columns, 1 with date, 1 with name and 1 with score out of five.

I want to make a graph on another sheet. I basically want a formula to look at the date column, if it is between 01/01/07 and 31/01/07 to look in column c and count the number of 1s and then I can adapt it to count number of 2,3,4 etc

I have got this far...


=COUNTIF(Data!$A$2:Data!$A$14733,"<="&DATE(2007,01,31),C2:C4000)-COUNTIF(Data!$A$2:Data!$A$14733,"<"&DATE(2007,01,01),C2:C4000)

But this is not gonna count the number of times 1 appears. Heeeeeellllp please

 

[barry houdini]

Try

=SUMPRODUCT(--(Data!$A$2:$A$14733<=DATE(2007,01,31)),--(Data!$A$2:$A$14733>=DATE(2007,01,01)),--(Data!$C$2:$C$14733=1))