11

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!

Functionality

Comments

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.

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

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. yes

 

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. laugh

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

laugh