Current Month Spending Formula

A Rose

Hello Community,

How do I calculate how much was spent this month using the below columns?

[Spending Date] Date Format

[Spedning Amount] USD Format

Something like =SUMIF([Spending Date]:[Spending Date],(MONTH(TODAY())), [Spedning Amount]:[Spedning Amount]

The Issue is that (MONTH(TODAY())) returns the current month in a single digit number like 2 for Feburary, and the date column has the full dates (Default 02/08/2022, mm/dd/yyyy).

Thank you!

Jeff Reisman

Instead of trying to pull the Month out the date range, you could build start and end dates inside your formula to compare to the Date field. We'll need to change to SUMIFS, because we'll have multiple criteria. With SUMIFS, the range we want to add up comes first, followed by criteria ranges and criteria.

Try this:

=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date], <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31))

In English: Add up the Spending Amount for rows where the Spending Date is greater than or equal to the first day of the current month, and where the Spending Date is less than or equal to the last day of the current month.

The other way to do this would be to add a helper column that gets the Month from the date field for each row, then compares that to the current month to find which rows to add up. I would say you'd want to do Month and Year, so that you're pulling from this February and not last February too.

A Rose

Hi,

I ended up using 3 summary columns to assist with the formula, using Right and Left functions.

Jeff Reisman

Instead of trying to pull the Month out the date range, you could build start and end dates inside your formula to compare to the Date field. We'll need to change to SUMIFS, because we'll have multiple criteria. With SUMIFS, the range we want to add up comes first, followed by criteria ranges and criteria.

Try this:

=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date], <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31))

In English: Add up the Spending Amount for rows where the Spending Date is greater than or equal to the first day of the current month, and where the Spending Date is less than or equal to the last day of the current month.

The other way to do this would be to add a helper column that gets the Month from the date field for each row, then compares that to the current month to find which rows to add up. I would say you'd want to do Month and Year, so that you're pulling from this February and not last February too.

A Rose

Thank you @Jeff Reisman!

A Rose

@Jeff Reisman ,

would this formula affect a month that doesn't have 31 days?

Thanks

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319054#Comment_319054

Well, LOL, here's what happens when you put this in a cell during February:

=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31)

So, about using that helper column... 😂

In your "Helper" column:

=IFERROR(VALUE(MONTH([Spedning Date]@row) + "" + YEAR([Spedning Date]@row) + ""), "")

For a February 2022 spending date, you'd get a value of 22022. for rows with a blank spending date, you get a blank Helper cell.

Then for your SUMIFS formula:

=IFERROR(SUMIFS([Spedning Amount]:[Spedning Amount], Helper:Helper, VALUE(MONTH(TODAY()) + "" + YEAR(TODAY()) + "")), "")

Add up Spending Amounts where the Helper column matches today's month and year in the format of "monthyear", 22022.

A Rose

@Jeff Reisman

can we add a OR formula that if it's not 31 days, so we don't need additional columns?

Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319057#Comment_319057

An OR won't work there. With an OR, the system would still consider a row dated 3/1/22 through 3/3/22 to be in the month of February. You'd have to get a bit crazy with some nested IFs in there:

=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date],  IF(MONTH(TODAY()) = 2, <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 28), IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 6, MONTH(TODAY()) = 9, MONTH(TODAY()) = 11), <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 30), IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 3, MONTH(TODAY()) = 5, MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 10, MONTH(TODAY()) = 12), <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31), ""))))

Give it a shot, maybe it will work! No guarantees, no time left for me to test it today! 😀

A Rose

Hi,

I ended up using 3 summary columns to assist with the formula, using Right and Left functions.