Auto sum for YTD

Gaurav Chauhan

Hi Team

Can you please help with a formulae wherein on 1st of each month, it can sum on the values for all the previous months. I have the the exact format and month type in the sheet like in the snippet below. I do have 2 helper columns and a Automation to update Today's date and subsequent month based on number.

Can you please suggest a formulae so that on 4/1 it calculates 120 and on 5/1 it calculates 200

KPH

Hi @Gaurav Chauhan

A SUMIF function will do this if you can change the month column to either

• a date column containing the 1st of the month
• the numeric reference for the month (1 instead of Jan)

Making either of these changes will allow you to SUM IF either

• the date is before the date you are looking for
• the month number is less than the month number you are looking at

https://help.smartsheet.com/function/sumif

Gaurav Chauhan

Thank you. Can you also help with the SUMIF formulae string so I can make that suggested change and test it.

KPH

Can you tell me what you changed your month to? A date, or a number?

Gaurav Chauhan

Thanks. I change it to number 1 through 12.

KPH

If your months are in a column called .Month.. , your numbers to sum are in a column called Sum, and your dates are in a column called On, this formula will sum the values in the On column if the number in the .Month.. column on the row is less than the month of the date in the On column.

=SUMIF([.Month..]:[.Month..], <MONTH(On@row), Sum:Sum)

It looks like this: