conditional SUM by dates and values

[emilydoak]

Hi Everyone,

I have a worksheet with a column of dates (B6:B74) and multiple
columns with values (C6:C74, D674, E6:E74, etc.). The dates range
from 12-31-05 to 3-2-2009. The values are both positive and
negative.

I would like to create formulas in row 75 to sum the values from each
column (starting with column C) that are both negative and occur after
12-31-08.

I know I could do this by copying the negative values to a new column
and using SUMIF to sum after 12-31-08. However, I have multiple
columns with values, so I would like to put the formulas into one cell
per column to avoid creating new columns.

Here is an example of my worksheet (with just one column of values):

11/2/2007 -15
5/3/2008 5
12/18/2008 4
2/14/2009 -5
3/1/2009 16

The answer here should be -5.

Thanks so much for your help!!!!!

Emily

 

[Pete_UK]

Put this in C75:

=SUMPRODUCT(($B6:$B74>DATE(2008,12,31))*(C6:C74<0),C6:C74)

Then copy across into D75 and beyond (depending on how many columns of
values you have).

Hope this helps.

Pete

 

[emilydoak]

Thanks! It works!

However, do you know if there is a way to reference the date to
separate cell? In the future, I might have to change 12-31-08 to
3-31-08 and it could be a pain to change the date for each column.

 

[Dave Peterson]

=SUMPRODUCT(($B6:$B74>X99)*(C6:C74<0),C6:C74)

Change x99 to the address of the cell that contains the date.

 

[Pete_UK]

You're welcome, Emily.

I would suggest using B75 to enter the cut-off date (maybe format that
cell with a differrent colour to distinguish between the other dates),
and then use this formula in C75:

=SUMPRODUCT(($B6:$B74>$B75)*(C6:C74<0),C6:C74)

and then copy it across.

Hope this helps.

Pete

 

[emilydoak]

That's perfect! Thank-you so much for your help!

 

[Pete_UK]

Glad to hear it - thanks for feeding back, Emily.

Pete