0
Thanks

A few words of thanks would be greatly appreciated.

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 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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

0 Comments