Adding data from one column using another

December 2016



Issue


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:

=IF(AND(criteria<range,range<=criteria),cell,)
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.

Solution


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

=SUMPRODUCT(($A$1:$A$200<"1/12/09"+0)*($A$1:$A$200>"1/6/09"+0)*($B$1:$B$200))


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.

Note


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Adding data from one column using another » from CCM (ccm.net) 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.