ExcelBanter - Sumproduct

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sumproduct (https://www.excelbanter.com/excel-worksheet-functions/153778-sumproduct.html)

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"))

Sandy

Sandy,

Your formula works fine for me. It actually looks at 2 sets of 3 ranges and
returns how may times your constants appear in a single column.

Mike

Mike

"Sandy" wrote:

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"))

Sandy