SUMIFS  #Invalid Operation  Why?
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
Best Answer

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

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

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!