problem using "<=" with SUMPRODUCT - PLEASE HELP

[Guest]

This problem has left me stumped for the past 3 days. I hope someone can
help. I have two columns. The first column (Column A) is coded with either
a "P" or an "X". The second column (Column B) contains percentages. I've
been trying to count the rows that have a "P" in Column A AND are less than
or equal to 25% in column B. The formula I used was:

=SUMPRODUCT((A3:A92="p")*(B3:B92<=25%))

The total came up to 9, but the actual result is 1 - there is only one row
with a "P" in Column A and a percentage of "25%". When I took out the "<"
symbol, it counted the correct row and gave me a total of 1.

Anyone have any clues as to how I can get the correct total for this
percentage range?

 

[Guest]

Probably because you have 8 records that have a "P" in column A and are
strictly less than (<) 25%.

 

[Guest]

Thanks for your help, David.

This helps explain the result, so I guess the new quesion is:

How do I revise the formula so that it counts any percentage in Column B

=1%, but <=25%

 

[Domenic]

Maybe that's because the numbers in Column B are actually more than two
decimal places, even though only two decimals are displayed. Try
increasing the decimal places for your cells to see if this is the case.
If so, you may want to use the ROUND function to round these numbers to
two decimal places.

Hope this helps!

 

[Guest]

Hi,

Try the following array formula (Ctrl+Shift+Enter)

=sum(if((A3:A92="p")*(B3:B92>=1%)*(B3:B92<=25%),1,0))

Regards,

Ashish Mathur