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