Dynamic Year to date formula

**tomjoe** · 08-25-2012, 11:12 AM

Hi all

I have a spreadsheet with products in column A, months in column B and sales in column C.
In column D I want to calculate the YTD sale, but just show the YTD sale in the actual month (row) we are in now.
I try to make a formula that I can send all the way down column D, but then the formula must handle shifting products in column A.
But I get stucked and cannot figure out how to make this work.
See example in the attached file.

Any ideas?

**oeldere** · 08-25-2012, 11:21 AM

I would solve this with an pivot table.

See the attachment.

**tomjoe** · 08-25-2012, 11:36 AM

Thank you Oeldere

I appreciate your suggestion. It's simple and good.
A pivot table is a possible solution, but I am interested in a formula that can tackle my specific need in the exact table format attached.

**oeldere** · 08-25-2012, 11:44 AM

maybe, llike this.

See the answer in column D (golden cells).

**tomjoe** · 08-25-2012, 12:24 PM

Thank you.
You got me in the right direction.
I added the if month formula to get the YTD number only in todays month
= if(month(B2)=8;sumif($A$2:$A20;$A20;$C$2:$C20);"").

**tomjoe** · 08-25-2012, 05:13 PM

Originally Posted by tomjoe

Thank you.
You got me in the right direction.
I added the if month formula to get the YTD number only in todays month
= if(month(B2)=8;sumif($A$2:$A20;$A20;$C$2:$C20);"").

I made the formula completely dynamic:
= if(month(B2)=month(today()));sumif($A$2:$A20;$A20;$C$2:$C20);"")

**oeldere** · 08-25-2012, 06:16 PM

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

If you have (other) questions, on this item, just ask.