I need help trying to get a formula right.

On a worksheet named "GM", column A contains dates. Column E contains
numbers which I eventually want summed. Column H contains either the # 1, 2,
or 3.

On another worksheet, column A contains start dates and column B contains
end dates. In column C, I want to look at all rows of sheet "GM" and sum
column E if the date of that row falls between the start and end dates on the
current sheet AND if column H = 1.

Here's what I have but all I get is "#VALUE":

=SUMPRODUCT(GM!H2:H224=1,AND(GM!A2:A224=A2,GM!A2: A224<=B2),(GM!E2:E224))

=SUMPRODUCT(--(GM!H2:H224=1),--(GM!A2:A224=A2),--(GM!A2:A224<=B2),GM!E2:E22
4)



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hankinator" wrote in message
...
I need help trying to get a formula right.

On a worksheet named "GM", column A contains dates. Column E contains
numbers which I eventually want summed. Column H contains either the # 1,
2,
or 3.

On another worksheet, column A contains start dates and column B contains
end dates. In column C, I want to look at all rows of sheet "GM" and sum
column E if the date of that row falls between the start and end dates on
the
current sheet AND if column H = 1.

Here's what I have but all I get is "#VALUE":

=SUMPRODUCT(GM!H2:H224=1,AND(GM!A2:A224=A2,GM!A2: A224<=B2),(GM!E2:E224))

Thank you so much for the fast response! It works beautifully!

"Bob Phillips" wrote:

=SUMPRODUCT(--(GM!H2:H224=1),--(GM!A2:A224=A2),--(GM!A2:A224<=B2),GM!E2:E22
4)



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hankinator" wrote in message
...
I need help trying to get a formula right.

On a worksheet named "GM", column A contains dates. Column E contains
numbers which I eventually want summed. Column H contains either the # 1,
2,
or 3.

On another worksheet, column A contains start dates and column B contains
end dates. In column C, I want to look at all rows of sheet "GM" and sum
column E if the date of that row falls between the start and end dates on
the
current sheet AND if column H = 1.

Here's what I have but all I get is "#VALUE":

=SUMPRODUCT(GM!H2:H224=1,AND(GM!A2:A224=A2,GM!A2: A224<=B2),(GM!E2:E224))