SUMIFS: known issues?

Question

Level 1

4 points

SUMIFS: known issues?

I have converted an .xls-sheet (that had been created on a windows PC) to a numbers table. The LIST üproperties that wre lost in the course of the conversion I try to compensate with a series of SUMIFS (SUMMEWENNS) operations on a seperate table, e.g.:

=SUMMEWENNS(Tabelle 1::$B2:$B347;Tabelle 1::$F2:$F347;D$1;JAHR(Tabelle 1::$A2:$A347);$A4;MONAT(Tabelle 1::$A2:$A347);">=1";MONAT(Tabelle 1::$A2:$A347);"<=3")

I get an error message:

"SUMIF requires that all range arguments are the same size."

I see neither an error in above operation nor in the referral to the range objects that are all of the same size. the community is wiser than me. I ask for help. I use numbers 14.2(7041.0.109) on macOS 14.6.1

MacBook Pro 13″, macOS 14.6

Posted on Nov 5, 2024 10:34 AM

Reply

Answer 1

Badunit

Level 7

28,054 points

Nov 5, 2024 11:13 AM in response to bernd41

Number does not have Excel's array functions. JAHR(Tabelle 1::$A2:$A347) is a singular value in Numbers, not an array of 346 values. Same with MONAT. Those are the range arguments that are not "the same size"

Instead of checking for the year then the month, do the last three checks as $A2:$A347 >= DATE($A4,1,1) and $A2:$A347 < DATE($A4,4,1)

I did not test this formula but this is what I'm getting at:

=SUMMEWENNS(Tabelle 1::$B2:$B347;Tabelle 1::$F2:$F347;D$1;Tabelle 1::$A2:$A347;">="&DATUM($A4,1,1);Tabelle 1::$A2:$A347;""<"&DATUM($A4,4,1))

Reply

Answer 2

bernd41 Author

Level 1

4 points

Nov 6, 2024 1:37 AM in response to Badunit

The formula works fine after I regionalized some "," to ";". Thanks Badunit!

Reply

SUMIFS: known issues?

Similar questions