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

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Carsten Hempelt

Hi Paul,

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.