SUMPRODUCT Within A Specific Date Range

**BoardGuy** · 05-13-2013, 03:26 PM

I'm stumpped. I've been trying to complete a SUMPRODUCT formula that will only pull data if the date is within 6 months or 180 days. I've been trying to combine a COUNTIF with a nested SUMPRODUCT, but haven't had any luck. I've got a SUMPRODUCT formula that works to pull all entries, but I cannot get it to work with a specific date range (namely TODAY minus 180 days).

Here is the SUMPRODUCT formula that works:
=(SUMPRODUCT(($J$12:$J$10000)*1)/3)

The "/3" is meant to divide the product into thirds for tracking purposes.

Any ideas?

**Pete_UK** · 05-13-2013, 03:32 PM

Assume your dates are in column D, then try this:

=SUMPRODUCT((D$12:D$10000<=TODAY())*(D$12:D$10000>TODAY()-180),$J$12:$J$10000)/3

Hope this helps.

Pete

**DonkeyOte** · 05-13-2013, 03:44 PM

see also SUMIFS if using XL2007 or above - quite a bit more efficient than SUMPRODUCT - especially when using Volatile precedents like TODAY()

Formula:

Please Login or Register  to view this content.

**BoardGuy** · 05-13-2013, 04:19 PM

Pete - Your answer worked like a charm! Didn't get a chance to try the SUMIFS, however I appreciate your response as well, DonkeyOte. I originally was using a SUMIFS for this formula, but couldn't get the product to divide by 3, so it was recommended to go to a SUMPRODUCT.

Thank you both for the prompt responses!