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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!