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