Adding data from one column using another

February 2017



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


Published by aakai1056. Latest update on November 8, 2012 at 04:08 PM by Jeff.
This document, titled "Adding data from one column using another," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).