1

I recently switched to Numbers from Mac Excel and a formula is not working. I am taking the sum of a range of cells where a SUMIF condition is met. The formula worked in Mac Excel but Apple Numbers returns an error "The formula contains a number outside the valid range." It's obvious that SUMIF condition syntax is not valid in Numbers, but I cannot find any documentation or example to correctly format it. I have looked at SUMIFS with the same result.

For example, this works in Numbers

SUM(SUMIF(A2:A114,{"Blue"},F2:F114))

but this does not

SUM(SUMIF(A2:A114,{"Blue","Green","Black"},F2:F114))

Please use a working formula example in your answer. Thanks.

Improve this question

2 Answers 2

Reset to default
2

You can use REGEX:

SUMIF(A2:A114,REGEX("Blue|Green|Black"),F2:F114)
Improve this answer
0

This works.

SUM(SUMIF(A2:A114,"Blue",F2:F114),SUMIF(A2:A114,"Green",F2:F114),SUMIF(A2:A114,"Black",F2:F114))
Improve this answer
2
  • Thx for posting your solution. I don't think Apple claims Numbers can fully replace Excel in functionality. However, Numbers can still do a lot of things and it also has some nice features that Excel does not have plus it comes free with any Apple device running iOS / iPadOS / macOS!
    – Alper
    Commented Apr 6, 2021 at 18:05
  • Excellent points. Can't beat free over another hundred-plus bucks to upgrade Mac:Excel.
    – Gregory Bologna
    Commented Apr 6, 2021 at 19:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .