SUMPRODUCT need to add " ' " in front of a digit?

[Lewis Koh]

Hi,
I realized I need to add " ' " to the list with 240 in order for the SUMPRODUCT to count properly. Does any one knows where to find information on why " ' " is needed? Are there any other characters that needed " ' " in front for SUMPRODUCT to work?

=SUMPRODUCT((Q11:Q83="240")*(Z11:Z83>0)

[teylyn]

I assume you are talking about the part

Q11:Q83="240"

You are comparing the cells in column Q with a text. If column Q has numbers, you either need to coerce these numbers into text, for example by preceding them with a ' sign, or, you could simply compare with a number

Q11:Q83=240

Post a data sample if you have more questions, because this really depends on the data types in your cells. Remote analysis can only go so far...

[Lewis Koh]

Hi, yes you are right. My cell is a number. So it only affect a number cell. Does 20R consider as a number cell?

[teylyn]

Does 20R consider as a number cell?

No. A number is something that you can use in a calculation. Try a mathematical operation on A1 if A1 = 20R - for example =A1/2. If you get a number as a result, then the cell is a number. If you get an error message, then the cell is not a number.

Numbers are only the digits 0 to 9, with the decimal separator of your regional settings, i.e. either comma or dot. Formatting can add a thousand separator or other characters, but that does not change the underlying data type.

[Lewis Koh]

oh i see. Thanks for the clarification. I understand now :-)

[shg]

Lewis, please stop quoting whole posts. It's just clutter.

Thanks.

[Lewis Koh]

oh, sorry.