|
SUMPRODUCT issue
I have a table whe
Col C is a text field Col D is a text field Col E is a date field Col H is a numeric field I want to find rows that match multiple criteria & sum the value in Col H. I am using the following formula: =SUMPRODUCT(--(Sheet1!E$1:E$9999=A10),--(Sheet1!E$1:E$9999<A11),--(Sheet1!C$1:C$9999=C$6),--(Sheet1!D$1:D$9999="A"),Sheet1!H$1:H$9999) This is returning #VALUE. TIA -- Tony |
Hi Tony,
It sounds like you have a #VALUE in one of the cells being checked, E, C, D or H. Try refining your rows, 1-500, 1-250, 500-250, and so on until you find where the problem is and correct it. You can find which column by testing each like =SUMPRODUCT(--ISERROR(E1:E9999)) -- HTH RP (remove nothere from the email address if mailing direct) "TonyL" wrote in message ... I have a table whe Col C is a text field Col D is a text field Col E is a date field Col H is a numeric field I want to find rows that match multiple criteria & sum the value in Col H. I am using the following formula: =SUMPRODUCT(--(Sheet1!E$1:E$9999=A10),--(Sheet1!E$1:E$9999<A11),--(Sheet1!C $1:C$9999=C$6),--(Sheet1!D$1:D$9999="A"),Sheet1!H$1:H$9999) This is returning #VALUE. TIA -- Tony |
Thanks Bob. That was the problem.
-- Tony "Bob Phillips" wrote: Hi Tony, It sounds like you have a #VALUE in one of the cells being checked, E, C, D or H. Try refining your rows, 1-500, 1-250, 500-250, and so on until you find where the problem is and correct it. You can find which column by testing each like =SUMPRODUCT(--ISERROR(E1:E9999)) -- HTH RP (remove nothere from the email address if mailing direct) "TonyL" wrote in message ... I have a table whe Col C is a text field Col D is a text field Col E is a date field Col H is a numeric field I want to find rows that match multiple criteria & sum the value in Col H. I am using the following formula: =SUMPRODUCT(--(Sheet1!E$1:E$9999=A10),--(Sheet1!E$1:E$9999<A11),--(Sheet1!C $1:C$9999=C$6),--(Sheet1!D$1:D$9999="A"),Sheet1!H$1:H$9999) This is returning #VALUE. TIA -- Tony |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com