Between using Sumproduct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee - 06 Nov 2007 18:07 GMT

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.

Signature

Lee Coleman

Reply to this Message

Peo Sjoblom - 06 Nov 2007 18:15 GMT

=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

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 4 lines]
> when I try them in the same sumproduct() it fails.
> Thanks for reading and any information to the right direction.

Reply to this Message

Lee - 06 Nov 2007 18:40 GMT

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

> =SUMPRODUCT(--(A2:A500>=DATE(2007,5,1)),--(A2:A500<=DATE(2007,8,31)),B2:B500)
>
[quoted text clipped - 21 lines]
>> when I try them in the same sumproduct() it fails.
>> Thanks for reading and any information to the right direction.

Reply to this Message

Peo Sjoblom - 06 Nov 2007 20:14 GMT

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

Signature

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
[quoted text clipped - 29 lines]
>>> but, when I try them in the same sumproduct() it fails.
>>> Thanks for reading and any information to the right direction.

Reply to this Message

Rate this thread: