Between using Sumproduct

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

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.
 
=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
<
Click to expand...

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
 
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 said:
=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
<
Click to expand...

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
Click to expand...
 
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 data>text 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 said:
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
Click to expand...
 
Back
Top