Adding data from one column using another

December 2016


I am having a real problem with an Excel formula.

I have a worksheet with variable rows and a number of columns. I need to add together the values of particular cells in one column, but only if corresponding cells in another column fit within a date range. I need to do this for all rows (although I won't know how many rows there are until I have imported the data), and for different date ranges. I need to add the values together on a separate sheet.

The reason for this is that I want to sort activity put through a particular account into weeks. The data is input with a date, but i need all data up to and including the week end to add together to give me the week's total. I need to do this for each week of the year.

I have tried:

but realized that I would have to re-type this for each row. There could be as many as 400 rows, and never less than 110.


Supose your data is A1 to B15 like this :

1/1/2009 1
1/2/2009 2
1/3/2009 3
1/4/2009 4
1/5/2009 5
1/6/2009 6
1/7/2009 7 ****
1/8/2009 8
1/9/2009 9
1/10/2009 8
1/11/2009 7 ****
1/12/2009 6
1/13/2009 5
1/14/2009 4
1/15/2009 3

In any empty cell outside these two columns copy paste this formula


you will get 39

"***" in column c is only to show you the borders to check the sum

I have made provision for maximum of 200 rows. if necessary modify the formula.


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Adding data from one column using another » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.