Formula calculating monthly totals

steve50951

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!

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

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.