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!D6:D2 99="LEARNING WORLD"),1))

Does anybody know why please?

Thanks in advance,

Mike

Your formula is fine. enter it as an array formula

ie SHIFT+CTRL+ENTER

"Mike Halsey" wrote:

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!D6:D2 99="LEARNING WORLD"),1))

Does anybody know why please?

Thanks in advance,

Mike

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

"Mike Halsey" wrote:

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!D6:D2 99="LEARNING WORLD"),1))

Does anybody know why please?

Thanks in advance,

Mike

On Mon, 21 Sep 2009 03:28:23 -0700, Mike Halsey
wrote:

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!D6:D 299="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!D6:D15="LEA RNING 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!D6:D 15="LEARNING
WORLD"))

Hope this helps / Lars-Åke.