SUMIFS - #Invalid Operation - Why?

Carsten Hempelt

Hello community,

there is a formula problem which troubles me slightly.

These formulas work perfectly fine

=COUNTIFS({6M_category}, HAS(@cell, [Primary Column]@row), {Time of occurence}, >=[Start Date]#, {Time of occurence}, <[End date]#)

&

=SUMIF({Impact_to_production_hrs}, >0)

BUT this one, does not and returns "#invalid operation"

=SUMIFS({Impact_to_production_hrs}, >0, {Time of occurence}, >=[Start Date]#, {Time of occurence}, <[End date]#)

Does anybody has an idea of why the SUMIFS formula does not work as expected? SUMIFS is using the same arguments and Syntax as the COUNTIFS function, I believe.

Any help or ideas are appreciated,

Carsten

Carsten Hempelt

Hi Paul,

thanks a lot. This works like a charm. So the Syntax is not the same. Good to know.

Paul Newcome

The SUMIFS should be starting with 2 ranges. The range to sum and then the first range to evaluate.

=SUMIFS({Impact_to_production_hrs}, {Impact_to_production_hrs}, >0, {Time of occurence}, >=[Start Date]#, {Time of occurence}, <[End date]#)

Carsten Hempelt

Hi Paul,

thanks a lot. This works like a charm. So the Syntax is not the same. Good to know.

Paul Newcome

That is correct. The reason for this is that you may not necessarily need to always establish some kind of criteria for the range you want to sum. So we establish the range to sum first, THEN proceed with the range/criteria sets similar to the COUNTIFS syntax.