Adding data from one column using another

Solved/Closed
saltod
Posts
2
Registration date
Tuesday August 25, 2009
Status
Member
Last seen
August 27, 2009
- Aug 26, 2009 at 01:40 PM
saltod
Posts
2
Registration date
Tuesday August 25, 2009
Status
Member
Last seen
August 27, 2009
- Aug 27, 2009 at 02:57 AM
Hello,

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 seperate 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.

Any help would be appreciated.

Thank you

Dominic


1 reply

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Aug 26, 2009 at 08:05 PM
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.
0
saltod
Posts
2
Registration date
Tuesday August 25, 2009
Status
Member
Last seen
August 27, 2009

Aug 27, 2009 at 02:57 AM
That's great, I've tried it and it works no problem. You have saved me hours of inputting, thank you.
0