i want to add up the cells in 1 column that have a specific date range in a different column.. can anyone help. The truth is out there.. somewhere...

Hi, Here is a way to get the sum of values in date ranges:

Create a horizontal table of first and last dates in each date range.

I created this table for 6 date ranges

First start date in cell J2

First end date in cell J3

The next pair is in cells K2 and K3 and so on to O2 an O3I had 200 dates in column G from G3 to G202

I had 200 values in column H from H3 to H202The value column does not have to be adjacent to or to the right of the date column, but it must have the same range of rows.

In cell J4 underneath your first date range enter this formula:

=SUMPRODUCT(($G$3:$G$202>=J2)*($G$3:$G$202<=J3)*($H$3:$H$202))Note the $ signs - they are required, so that the formula will refer to the correct cells when it is dragged to extend it.

Select Cell J4 and drag it to extend it underneath your date ranges in my example to cell O4You will now have the totals for each of the six date ranges.

(all dates must be recognized by Excel as dates for this to work)

SUMPRODUCT() returns 1 if a comparison is match, zero if it isn't, so the first two parts of the formula return 1 or 0 for each date comparison in column G, in turn. When a date returns a zero from either of the first two parts of the formula, as it multiplies the parts together, anything times zero is zero, so the last part of the formula $H$3:$H$202 is only added if the date matches both criteria.

If a date is 'in-range' the result is 1 * 1 * value in column H

If a date is not 'in-range' the result is 1 * 0 * value, or 0 * 1 * value, which is of course zero.SUMPRODUCT() repeats this calculation for every row in the range, and adds each result as it goes along.

Regards

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History