Is there a way to enter a date value that is between 2 dates in sumproduct
or another function that will look at the same column and return the sum of
a different column? If the value is greater than May 1,2007 but less than
August 31, 2007 in column A it will return the sum of the matching rows in
column B.
I have tried this with either greater than OR less than and it works but,
when I try them in the same sumproduct() it fails.
Thanks for reading and any information to the right direction.

--
Lee Coleman

=SUMPRODUCT(--(A2:A500=DATE(2007,5,1)),--(A2:A500<=DATE(2007,8,31)),B2:B500)


note that I included May 1st and August 31st since that is what I believe
you wanted, if not change the

<=
=

to
<


You can also use

=SUMIF(A2:A500,"="&DATE(2007,5,1),B2:B500)-SUMIF(A2:A500,""&DATE(2007,8,31),B2:B500)

which is probably a bit more efficient if you have a lot of cells to sum


--


Regards,


Peo Sjoblom



"Lee" wrote in message
...
Is there a way to enter a date value that is between 2 dates in sumproduct
or another function that will look at the same column and return the sum
of a different column? If the value is greater than May 1,2007 but less
than August 31, 2007 in column A it will return the sum of the matching
rows in column B.
I have tried this with either greater than OR less than and it works but,
when I try them in the same sumproduct() it fails.
Thanks for reading and any information to the right direction.

--
Lee Coleman

I am confused about the Date because the accounting program exports the
invoice date in MMDDYYYY. Will this work with the YYYYMD format that you
have here or do I need to change one of the formats? And it is alot of rows
and I am grateful for Excel 2007 that can handle the 95,000+ rows on one
worksheet.
Thank you for the feedback.
Lee

"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(--(A2:A500=DATE(2007,5,1)),--(A2:A500<=DATE(2007,8,31)),B2:B500)


note that I included May 1st and August 31st since that is what I believe
you wanted, if not change the

<=
=

to
<


You can also use

=SUMIF(A2:A500,"="&DATE(2007,5,1),B2:B500)-SUMIF(A2:A500,""&DATE(2007,8,31),B2:B500)

which is probably a bit more efficient if you have a lot of cells to sum


--


Regards,


Peo Sjoblom



"Lee" wrote in message
...
Is there a way to enter a date value that is between 2 dates in
sumproduct or another function that will look at the same column and
return the sum of a different column? If the value is greater than May
1,2007 but less than August 31, 2007 in column A it will return the sum
of the matching rows in column B.
I have tried this with either greater than OR less than and it works but,
when I try them in the same sumproduct() it fails.
Thanks for reading and any information to the right direction.

--
Lee Coleman

That's a stupid accounting program. MMDDYYYY is not dates as far as Excel
can tell
You can convert it but not through formatting which just change the display

Best way is probably to select A, then do datatext to columns, click next
twice and in step 3 under column data format select date and MDY from the
dropdown

Also if A2:A500 (replace by your actual range) do NOT have any empty cells
you can use

=SUMPRODUCT(--(--TEXT(A2:A500,"00\/00\/0000")=DATE(2007,5,1)),--(--TEXT(A2:A500,"00\/00\/0000")<=DATE(2007,8,31)),B2:B500)

If there are empty cells you can use


=SUM(IF(A2:A500<"",IF(--TEXT(A2:A500,"00\/00\/0000")=DATE(2007,5,1),IF(--TEXT(A2:A500,"00\/00\/0000")<=DATE(2007,8,31),B2:B500))))


enter with ctrl + shift & enter



--


Regards,


Peo Sjoblom


"Lee" wrote in message
...
I am confused about the Date because the accounting program exports the
invoice date in MMDDYYYY. Will this work with the YYYYMD format that you
have here or do I need to change one of the formats? And it is alot of rows
and I am grateful for Excel 2007 that can handle the 95,000+ rows on one
worksheet.
Thank you for the feedback.
Lee

"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(--(A2:A500=DATE(2007,5,1)),--(A2:A500<=DATE(2007,8,31)),B2:B500)


note that I included May 1st and August 31st since that is what I believe
you wanted, if not change the

<=
=

to
<


You can also use

=SUMIF(A2:A500,"="&DATE(2007,5,1),B2:B500)-SUMIF(A2:A500,""&DATE(2007,8,31),B2:B500)

which is probably a bit more efficient if you have a lot of cells to sum


--


Regards,


Peo Sjoblom



"Lee" wrote in message
...
Is there a way to enter a date value that is between 2 dates in
sumproduct or another function that will look at the same column and
return the sum of a different column? If the value is greater than May
1,2007 but less than August 31, 2007 in column A it will return the sum
of the matching rows in column B.
I have tried this with either greater than OR less than and it works
but, when I try them in the same sumproduct() it fails.
Thanks for reading and any information to the right direction.

--
Lee Coleman