Sumproduct - Second Attempt

[Sandy]

I thought I had cracked the Sumproduct function but obviously not!

I have three ranges 1 - ("C39:K39,M39:U39") 2 - ("C40:K40,M40:U40") and
3 - ("C33:K33,M33:U33").

I am trying to count the instances where "Arrow" "Miss" and "Left" all occur
in the same column - I thought the following would work but it fails

=SUMPRODUCT(--($C$39:$K$39="Arrow"),--($C$40:$K$40="Miss"),--($C$33:$K$33="Left"))+SUMPRODUCT(--($M$39:$U$39="Arrow"),--($M$40:$U$40="Miss"),--($M$33:$U$33="Left"))

Even tried:-

=SUMPRODUCT(--($C$39:$K$39="Arrow")*($C$40:$K$40="Miss")*($C$33:$K$33="Left"))+SUMPRODUCT(--($M$39:$U$39="Arrow")*($M$40:$U$40="Miss")*($M$33:$U$33="Left"))

Thanks
Sandy

 

[Guest]

Sandy,

No need for a second attempt, you have an answer in you first and like you
first both these formula are fine.

Mike

 

[Sandy]

Yes you are quite right Mike - a senior moment or two - the sheet reference
wasn't included - it should read

=SUMPRODUCT(--(Sheet1!$C$39:$K$39="Arrow"),--(Sheet1!$C$40:$K$40="Miss"),--(Sheet1!$C$33:$K$33="Left"))+SUMPRODUCT(--(Sheet1!$M$39:$U$39="Arrow"),--(Sheet1!$M$40:$U$40="Miss"),--(Sheet1!$M$33:$U$33="Left"))

Apologies and Thanks too
Sandy

 

[Guest]

Sandy,

Senior (I actually prefer blonde) moments are a speciality of mine given my
advanced years but I still can't see anything wrong with your formula. What
error or unexpected result are you gettting.

Mike

 

[Sandy]

Hi Mike

I had forgotten to include the "Sheet1!" before each of the ranges - the
function is in a cell on Sheet2 not Sheet1.
So it was performing as it should have been ( no error message ) but
naturally wasn't finding any "Arrow", "Miss" or "Left" on "Sheet2" (because
they were on "Sheet1") therefore returning "0" when I knew it should have
returned at least "1".

Sandy