counting when two conditions apply

[mirrixx]

Hi,
I know of the function 'countifs', but this only is from ver2007. I use 2003 and this function is not available.
I would be grateful if someone could point me in the direction of applying the same type of rules in ver2003?

ie. In words, "count all the learners if the date is less than 12/03/2008 and is red. Need to ignore blank cells". See attached.

I tried sumproduct, but this counts blank cells

Thanks in Advance.

Mark

[daddylonglegs]

Hello Mark,

You can add an extra test to exclude blanks, e.g. use this formula in H3 copied across and down

=SUMPRODUCT(($B$3:$B$10=$G3)*($C$3:$C$10=H$2)*($D$3:$D$10<=$G$1)*($D$3:$D$10<>""))

where G1 contains your cut-off date 12/03/2008

[NBVC]

Try:

=SUMPRODUCT(--($B$3:$B$10=$G3),--($C$3:$C$10=H$2),--($D$3:$D$10<>""),--($D$3:$D$10<=DATE(2008,3,12)))

[TMS]

You seem to have three conditions.

Try:

=SUMPRODUCT(($B$3:$B$10=$G3)*($C$3:$C$10=H$2)*($D$3:$D$10<>"")*($D$3:$D$10<=$D$3))

Regards