Formula calculating monthly totals

I am trying to write a formula that will calculate receipt totals by month (starting this month)

Here is the formula i am using, but it is returning an invalid data type

=SUMIFS({Joseph Nelums- CRS Expense Reporting Receipt Total}, {Joseph Nelums- CRS Expense Reporting Date}, YEAR(@cell) = YEAR(TODAY()), {Joseph Nelums- CRS Expense Reporting Date}, MONTH(@cell) = 4)

Thanks for you help!

Screen Shot 2019-04-26 at 12.10.04 PM.png

Screen Shot 2019-04-26 at 12.12.24 PM.png

Find more posts tagged with

Formulas

Comments

Mike Wilday

Check to make sure the column you have the formula in is a Text/Number column. The #INVALID Data Type error is caused by a specific type of data not fitting the requirements for a column. Is it set up as a text/number column? Right-click on the column header to see the column type.

steve50951

it is setup for a text/number column.

SGF

L_123

Make sure the ranges are equal in the amount of cells contained in them. different sized ranges will return an error.

Paul Newcome

Any blanks within the ranges referencing dates (to include empty rows at the bottom of the sheet if referencing an entire column) will also throw an error.

Try wrapping the YEAR and MONTH requirements in an IFERROR statement.

IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

IFERROR(MONTH(@cell), 0) = 4

steve50951

I tried the above formulas. When i used them individually, they work. But when i combine them, i get an "incorrect argument set"

any ideas?

Ive attached a few screen shots...

thanks,

SGF

Screen Shot 2019-05-01 at 8.05.06 AM.png

Screen Shot 2019-05-01 at 8.05.16 AM.png

Screen Shot 2019-05-01 at 8.05.54 AM.png

Screen Shot 2019-05-01 at 8.06.01 AM.png

Screen Shot 2019-05-01 at 8.06.43 AM.png

Screen Shot 2019-05-01 at 8.06.52 AM.png

Paul Newcome

It is because your syntax is a little off. When using a SUMIFS, you specify the range to sum, then alternate between range and criteria. To save space and time when referencing the same range more than once, you can use an AND function for the criteria. Try something like this...

=SUMIFS({Range to sum}, {Criteria Range}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0) = 4))

steve50951

Ah.Gotcha. I got it. Yeah that works. Learning little by little.

thanks for you help!

Have a good one!

SGF

Paul Newcome

No worries. Happy to help.

My best advice is patience, a nice and sturdy brick wall for beating your head against, and plenty of pain killers for the ensuing migraines from said wall.

steve50951

Ha!!! Well said !!!!

Mike Wilday

I, however, prefer a bulldozer to completely destroy the wall. It saves your head, yet still allows you the physical release of aggression.

Paul Newcome

Very true, but the physical pain helps distract me from the mental anguish.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…