Sumif across columns-Sum pay-

**mremmenga** · 11-12-2008, 01:25 PM

I have data set up across multiple columns with dates included for each pay date, for example:

Person Pay-1 Date-1 Pay-2 Date-2 Pay-3 Date-3
002 10 1-2008 15 5-2008 30 10-2008
003 10 1-2008 20 4-2008 45 9-2008
004 5 2-2008 14 7-2008 55 8-2008

The sum I want to work would be as so: Sum pay-1 if date-1 is within 2 years of today, sum pay-2 if date-2 is within 2 years of today, etc. and so on to get a final sum value for each person based on when they were paid out in relation to today.

Any help would be MUCH appreciated. Thanks!

**NBVC** · 11-12-2008, 01:41 PM

Assuming your table occupies A1:G4 including Headers..

then try:

=SUMPRODUCT((C2:G2<=TODAY()+DATE(YEAR(TODAY())+2,MONTH(TODAY()),DAY(TODAY())))*(B2:F2<"Jan 1, 1901"+0)*(B2:F2))

Adjust ranges to suit.

in F2, copied down.

Sample attached

**mremmenga** · 11-12-2008, 02:27 PM

thank you for the quick reply NBVC!

The sum seems to be working just fine, but the "if" part of the date is not working. I think i tried to simplify it too much, let me add the rest of it. So if the data looked like this:

Person Pay-1 Date-1 Pay-2 Date-2 Pay-3 Date-3 Term Date
002 10 1-2005 15 5-2007 30 8-2008 9-2008
003 10 1-2005 20 4-2007 45 6-2008 9-2008
004 5 2-2005 13 7-2007 55 8-2008 9-2008

So basically what I want is a sum of person 002 based on their term date and the date-1/date-2/date-3 dates. So if "date-1" is within 2 years of the term date, i want that to be included in the sum. If the date is more than 2 years ago i dont want it to be included.

So the sums would be: (none of pay-1 would be included because date-1 is more than 2 years from the term date)

002 = 15+45 = 60
003 = 20+45 = 65
004 = 13+55 = 68

Thanks again for the help!

**NBVC** · 11-12-2008, 03:22 PM

Try then:

=SUMPRODUCT((C2:G2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))*(B2:F2<"Jan 1, 1901"+0)*(B2:F2))

copied down

**mremmenga** · 11-12-2008, 04:22 PM

NBVC, that seems to be working better thanks!
2 questions:

1-The formula ignores the first 2 columns of data to the right, and jumps right to the 3rd, any reason why it is doing that?

2- Each row ends at a different column, so if there are blank cells the formula will return an error. Any thoughts?

Thanks!

**NBVC** · 11-12-2008, 04:34 PM

Because the arrays have to be the same size for each of the sumproduct conditions, I am really just offsetting the ranges to keep the sizes the same... and because you have different alternating columns with same type of info, offsetting the range for the dates gets the right data in line in order to compare properly after Sumproduct has created the TRUE/FALSE resultant arrays.

Not sure what you mean by the 2nd question... can you give example of what you mean?

**mremmenga** · 11-12-2008, 04:37 PM

sure thing, so:

person 002 could have 4 values paid out, and 4 pay dates, for 8 columns
person 003 may only have 2 values paid out, and 2 pay dates, for 4 columns

the formula is copied for hundreds of rows, so the people like person 003 who have less data and thus blank cells in columns to the right get an error and no value for the formula, that make sense?

**NBVC** · 11-12-2008, 04:42 PM

I think as long as the formula takes into account the largest number of columns in all your data, then it will be fine for when there are less..

e.g if 4 values paid out, and 4 pay dates, for 8 columns is the most anyone could/does have, then adjust the initial formula to accomodate that

(e.g.

=SUMPRODUCT((C2:I2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))*(B2:H2<"Jan 1, 1901"+0)*(B2:H2))

and copy that down.. so even if a person had half as many dates/paid outs, it should still work for you.

Thread: Sumif across columns-Sum pay-

Thread Tools

Display

Sumif across columns-Sum pay-

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions