=COUNT(IF problem, please help

[Mike Halsey]

Hi,

I'm trying to get a traffic-light report to count the number of learners I
have on each of our contracts by their assigned traffic light status, but
it's not working

I've written this formula to count the number of learners with a traff-light
status of "red" AND the contract stating "learning world" but it's not
working.

=COUNT(IF((LEARNERS!C6:C299="RED")*(LEARNERS!D6299="LEARNING WORLD"),1))

Does anybody know why please?

Thanks in advance,

Mike

 

[Patrick Molloy]

Your formula is fine. enter it as an array formula

ie SHIFT+CTRL+ENTER

 

[RonaldoOneNil]

=SUMPRODUCT(--(LEARNERS!C6:C299="RED"),--(LEARNERS!D6299="LEARNING WORLD"))

 

[Lars-Åke Aspelin]

Hi,

I'm trying to get a traffic-light report to count the number of learners I
have on each of our contracts by their assigned traffic light status, but
it's not working

I've written this formula to count the number of learners with a traff-light
status of "red" AND the contract stating "learning world" but it's not
working.

=COUNT(IF((LEARNERS!C6:C299="RED")*(LEARNERS!D6299="LEARNING WORLD"),1))

Does anybody know why please?

Thanks in advance,

Mike

This formula has to be confirmed by CTRL+SHIFT+ENTER rather than just
ENTER.

The formula can be simplified to
=SUM((LEARNERS!C6:C15="RED")*(LEARNERS!D615="LEARNING WORLD"))

Remember to confirm by CTRL+SHIFT+ENTER.

You can also try this formula that can be confirmed by just ENTER:

=SUMPRODUCT((LEARNERS!C6:C15="RED")*(LEARNERS!D615="LEARNING
WORLD"))

Hope this helps / Lars-Åke.