Sum multiple Columns if Col Date <= PTD Date

[dawnmau]

I have tried the following (and variations thereof) "=SUMPRODUCT(--(TEXT($P$5:$AY$5,"mmm-yy")=TEXT($BD$4,"mmm-yy")),$P78:$AY78)"

My goal is to Sum up the cells in the row where the Column date in mmm-yy format <= to the Project to date Actual Date.

I figure the difference between the Budget and Actual would then be equal to Project to date Forecast

Can anyone spot my error? Is there another way to accomplish my goal?

File is attached

[ChemistB]

Try this;

=SUMPRODUCT(--(TEXT($P$5:$AY$5,"mmm-yy")+0<=TEXT($BD$4,"mmm-yy")+0),$P18:$AY18)

Does that work for you?

[NBVC]

Are you trying to convert everything to the first of the month?

This would work:

Please Login or Register  to view this content.

[dawnmau]

Actually ChemistB's worked. And I was SOOOOO close! Thanks for the fresh pair of eyes! You guys are great!

[NBVC]

Although it works for you now, note that:

=TEXT(BD4,"mmm-yy")+0

converts your June-10, to June 10th, 2010

it is taking the text string "Jun-10" and adding 0 to coerce is it makes it think that this is month and day.. and it assumes current year.

so if you had June-11 in BD4 it would convert that to June 11th, 2010.. which is not what you might have expected.

That is why I did not use that tempting method.

Actually, I am sure that Daddylonglegs will come up with an even better method to ensure accurate summation.

[ChemistB]

Ahh, didn't see that major snafu.
Nice catch NBVC!

[dawnmau]

If I need for it to be the End of the month do I substitute the 1 for 31?
=SUMPRODUCT(--(DATE(YEAR($P$5:$AY$5),MONTH($P$5:$AY$5),1)<=DATE(YEAR($BD$4),MONTH($BD$4),1)),$P18:$AY18) (Yours)

=SUMPRODUCT(--(DATE(YEAR($P$5:$AY$5),MONTH($P$5:$AY$5),31)<=DATE(YEAR($BD$4),MONTH($BD$4),31)),$P18:$AY18) (mine)

Will this work or will there be issues that I need to be aware of? and how do I correct?

[ChemistB]

I would suggest adding 1 to the month and removing the "=" sign so would be
< the first of the next month (as all months do not have 31 days)

=SUMPRODUCT(--(DATE(YEAR($P$5:$AY$5),MONTH($P$5:$AY$5),1)<DATE(YEAR($BD$4),MONTH($BD$4)+1,1)),$P18:$AY18)

Does that work for you?

[NBVC]

If the months don't have 31 days it goes over to next month beginning..

Try:

Please Login or Register  to view this content.

[dawnmau]

Thanks Guys! Will go with NBVC to head off trouble before it starts!