SUMPRODUCT issue

[Guest]

I have a table where:

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$9999="A"),Sheet1!H$1:H$9999)

This is returning #VALUE. TIA

 

[Bob Phillips]

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)

 

[Guest]

Thanks Bob. That was the problem.
--
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)