SUMPRODUCT Syntax (count cells that contain...)

[OverKnight]

I am trying to use SUMPRODUCT to obtain the number of times the letter “M” or “U” occur in a column of data. The cells with these letters will also have numbers, i.e., M-1234; however, the values in these cells will always start with these letters (if present). I’m having difficulty with the syntax. I’ve tried SUMPRODUCT('Sheet1'!I6:I1200="M*")*('Sheet1'!I6:I1200="U*"), but this returns a value of zero; I know that here are currently 21 occurrences of either “M” or “U”. Can anyone tell me what’s wrong here?

Thank you.

[Pete_UK]

Try it like this:

SUMPRODUCT((LEFT('Sheet1'!I6:I1200)="M")+(LEFT('Sheet1'!I6:I1200)="U"))

Note that + is equivalent to OR for arrays.

Hope this helps.

Pete

[Tony Valko]

Here's another one...

=SUM(COUNTIF(Sheet1!I6:I1200,{"M*","U*"}))

[OverKnight]

Thanks very much, Pete, and thank you for explaining the that + is equivalent to OR in array functions. Could I ask you to explain LEFT?

[OverKnight]

Thank you, Tony. This worked perfectly also; there's more than one way to skin a cat!

[Pete_UK]

LEFT takes characters from the left hand part of a cell. The full version is LEFT(A1,x), where x is the number of characters you want to take, but if it is missing it defaults to 1, so the function that I gave you just takes the first character within the cells of the range and then counts it if it is either "M" or "U".

Hope this helps.

Pete

[OverKnight]

Thanks again, Pete. Have a great New Year!

[Pete_UK]

Same to you - thanks for the rep.

Pete

[Tony Valko]

You're welcome. Thanks for the feedback!