1

I have a table with a date column and a value column like this

  |A          |B
1 |date       |value
2 |2013-12-10 |1
3 |2013-12-10 |3
4 |2013-12-09 |2
5 |2013-12-04 |5
6 |2013-12-02 |2

Dates can have gaps, duplicates, and don't necessarily need to be sorted.

Now I would like to add a third column, which gives the sum of those values, where the date is within a certain range from the row's date.

E.g. for a range of 6 days, I would expect the following

  |A          |B     |C
1 |date       |value |sum_6
2 |2013-12-10 |1     |6            // 1+3+2
3 |2013-12-10 |3     |6            // 1+3+2
4 |2013-12-09 |2     |7            // 2+5
5 |2013-12-04 |5     |7            // 5+2
6 |2013-12-02 |2     |2            // 2

Can this be done in a formula?

4

Enter the following into cell C2

=SUMPRODUCT(($A$2:$A$6>=A2-5)*($A$2:$A$6<=A2)*($B$2:$B$6))

then auto-fill downwards, by double-clicking the bottom right corner of cell c2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.