# Formula calculating monthly totals

edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭

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.

• it is setup for a text/number column.

SGF

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• 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

• ✭✭✭✭✭✭
edited 05/01/19

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))

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

thanks for you help!

Have a good one!

SGF

• ✭✭✭✭✭✭

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.

• Ha!!! Well said !!!!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!