2 range sum formulas based on one date

**Daphne** · 02-24-2010, 07:54 PM

In the attached spreadsheet example, is there a way to:

Reference the month and day ONLY of Clmn A:

Then have it auto create a sum formula . . .

I put the days of the year across (had to delect some clmns due upload size. . .), and at the end of that range per row, I need:

The next clmn to be SUM of Jan-1 to day before Date of Hire.
then
The next clmn to be Sum of Date of Hire to Dec-31.

This is so confusing to manually select like I did in the example.

Is there a way to get this to automatically select the range based on the date? (And then these sums will be referenced in another clmn as well. . .)

Any brave souls out there to try this?

I would really appreciate it!!!

Thank You!

- Daphne

**WHER** · 02-24-2010, 09:05 PM

Maybe like this?

**Daphne** · 02-24-2010, 09:41 PM

I can't wait to try this!! THANK YOU for your response!!!!!. . . It looks like it will do it. . . I can't wait to figure out what all of it means and reference all the correct cells in the orginal worksheet!!!! This web site is addicting, better than working puzzles!

Sincerely THANK YOU! I will post back shortly after I work it in the ss!!

- Daphne

**Daphne** · 02-24-2010, 11:24 PM

You are GREAT!!! How do you know this stuff!!!!!??????

This works on all dates except the December Dates??? I figure it has to do with being able to sum up to 255 (and I have 365 days of) columns, but why is it working for October and November???

So I have to work that out. Any suggestions? I'll be working on it all night, checking in every few!!!!!!!

!!

Sincerly!
Thanks!
Daphne

**DonkeyOte** · 02-25-2010, 04:26 AM

Daphne,

Late to the party but I'm not sure you can't use a standard SUMIF here, eg:

Code:

CF3: =SUMIF($B$2:$CE$2,"<"&DATE(YEAR($B$2),MONTH($A3),DAY($A3)),$B3:$CE3)
CG3: =SUM($B3:$CE3)-$CF3
copied down

Adjust ranges to suit of course.

The above is based on the premise that for the person in Row 6 of your file (start date 27-Mar-06) - if they have vacation booked on 15-Mar-10 this goes in the first sum rather than the second

**WHER** · 02-25-2010, 07:42 AM

Daphne,

You're right about the 255 column limit and the need to account for 365 days, i didn't think that through, it shouldn't work for october, november,december. Don's suggestion is (as usual) the simpler approach and works. This also answers your other question:" How do you know this stuff!!!!!??????" I learned this over here on the forum (and i don't mean making stupid mistakes like this).

**Daphne** · 02-27-2010, 03:50 PM

You both are "Gods" to me!!!! Don's example is working, I can't make it not work!!! Trust me, I'm good at making formulas not work!!! But WHER, I learned a lot from trying yours! I'm into this website! I'm a stay at home mom now, and I miss the 8 - 5++! That's why I'm doing this spreadsheet for a prior manager. It's been a challenge! Now my challenge is going to be regularly viewing the website, learning, and hopefully helping others!!!! I wish that everyone learns from this website like I am, not just taking advantage of the answers and still not knowing what they did!

Don, thank you for joining the party late!!! and I like your closing reading references! I did! Does the beer go to you?

Sincerely to you both!!!!
Cheers!

- Daphne