problem using "<=" with SUMPRODUCT - PLEASE HELP
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? |
problem using "<=" with SUMPRODUCT - PLEASE HELP
Probably because you have 8 records that have a "P" in column A and are
strictly less than (<) 25%. -- Regards, Dave "nmc1104" wrote: 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? |
problem using "<=" with SUMPRODUCT - PLEASE HELP
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% "David Billigmeier" wrote: Probably because you have 8 records that have a "P" in column A and are strictly less than (<) 25%. -- Regards, Dave "nmc1104" wrote: 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? |
problem using "<=" with SUMPRODUCT - PLEASE HELP
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! In article , "nmc1104" wrote: 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? |
problem using "<=" with SUMPRODUCT - PLEASE HELP
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 "nmc1104" wrote: 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? |
All times are GMT +1. The time now is 03:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com