Getting #Invalid Operation error

Megan TW

Hey all,

Trying to find the total amount of items passing inspection, in a month's time (and also by week). My formulas are below. I keep getting an invalid operation error and not sure why.

=SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Quantity Pass]:[Quantity Pass])

=SUMIF(Date:Date, >=(TODAY() - 7), [Quantity Pass]:[Quantity Pass])

Any help would be appreciated!

Nic Larsen

Try this: =SUMIFS([Quantity Pass]:[Quantity Pass], Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

=SUMIFS([Quantity Pass]:[Quantity Pass], Date:Date, AND(@cell <= TODAY(7), @cell >= TODAY()))

Mike Wilday

@Nic Larsen shouldn't that be a Today(-7)

=SUMIFS([Quantity Pass]:[Quantity Pass], Date:Date, AND(@cell <= TODAY(-7), @cell >= TODAY()))

Nic Larsen

@Mike Wilday Good catch. It's been too long of a week.

Mike Wilday

Haha. I feel ya!

Megan TW

:( Still getting the same error. It has to be something within the sheet itself, right? The syntax seems perfectly fine on both suggestions .

Nic Larsen

What type of column type do you have it set to? That's the only thing that comes to mind.

Formula in a text/number column type?

Date column set to a Date type of column?

Mike Wilday

According to formula errors,

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-operation

This error is due to operators.

But I am not seeing that. I also sometimes see this error when there are errors in the date column. If you have any errors in the date column you would want to wrap your Month queries in an IFERROR formula or resolve your errors in the column.

Megan TW

@Nic Larsen Date column is set to date type, "Quantity Pass" is a number column. Has a column formula to calculate total (=[Quantity Inspected]@row - [Quantity Fail]@row).

Nic Larsen

I'm stumped.

I created a column formula and it worked fine. Are you summarizing any data at the top/bottom of these ranges that it might be interacting with? Are both formulas getting the error or just one of them?

I updated this with an additional IFERROR as Mike mentioned above:

=SUMIFS([Quantity Pass]:[Quantity Pass], Date:Date, IFERROR(MONTH(@cell), 0) = IFERROR(MONTH(TODAY()), 0))

Megan TW

Yeah. I have the same formula in a different sheet with no issues. I'm totally lost.

I got the same for both the week and month formulas. Even after using the suggested formulas you all so graciously suggested. :/

Mike Wilday

If you scroll through your date column are you seeing any errors? Errors in your date column frequently throw Invalid Operation errors in SUMIFS and COUNTIFS when using them in the criteria.

Megan TW

I'm going to shake our data entry folks. I finally found it...instead of "0" someone had put "None" in the Quantity Pass column. UGH.

THANK YOU all!

Mike Wilday

LOL. That is great. Gotta love helpful descriptive people!