Adding data from one column using another
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 realised 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.
Excel data from column to row