How do I stop SUMIFS spilling in Numbers?

Question

Level 1

126 points

How do I stop SUMIFS spilling in Numbers?

I've read many articles on this community and other sites but cannot work out how to use Sumifs to sum a column of values where a date column is between two dates and to return a single answer without it spilling. I've done this for years. I am happy to take a completely different approach if someone can point me in the right direction.

Thank you for your help.

[Re-Titled by Moderator]

Original Title: How do I stop SUMIFS spilling?

Mac Studio, macOS 15.5

Posted on Jun 21, 2025 2:04 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Jun 21, 2025 4:37 AM in response to iwaddo

I think your SUMIFS syntax may be slightly off. It seems to be missing , and "" in strategic spots. This works here:

=SUMIFS(DataTable::B,DataTable::A,"<="&MAX(DataTable::A),DataTable::A,">="&C3)

You can also take the SUM of a spilling dynamic array function FILTER like this:

=SUM(FILTER(DataTable::B,DataTable::A>=C3))

(If you remove the SUM you will see the spill.)

SUMIFS - Apple Support

FILTER - Apple Support

SG

Reply

Answer 2

iwaddo Author

Level 1

126 points

Jun 21, 2025 4:52 AM in response to SGIII

Wow, what a schoolboy error. I've loads of other uses of SUMIFS with the correct syntax and seeing the 'spill' just sent me off down the wrong path rather than stepping back to find the syntax error.

I will have a play with FILTER.

Thank you for your help

Reply

Answer 3

iwaddo Author

Level 1

126 points

Jun 21, 2025 2:50 AM in response to iwaddo

Interestingly, this works

but this feels like a backward step

Reply

How do I stop SUMIFS spilling in Numbers?

Similar questions