Sometimes I have a formula where Numbers thinks it needs to notify my that it is using boolean values instead of numbers and give me a sticky blue triangle. How can I remove this?
I am using formula with FILTER and filtering for words in order so sum up a column.
= SUM( FILTER (ABS(A)) , (B = "foo") + (B= "bar") , 0 ))
With that formula–corrected to remove an extra )–there doesn't seem to be a way to remove the blue triangle.
Here are two ways to rewrite the formula to avoid the triangle:
=SUMIFS(ABS(A),B,"foo")+SUMIFS(ABS(A),B,"bar")
=SUM(MAP(A,B,LAMBDA(val,crit,IF(OR(crit="foo",crit="bar"),ABS(val),0))))
SG
With that formula–corrected to remove an extra )–there doesn't seem to be a way to remove the blue triangle.
Here are two ways to rewrite the formula to avoid the triangle:
=SUMIFS(ABS(A),B,"foo")+SUMIFS(ABS(A),B,"bar")
=SUM(MAP(A,B,LAMBDA(val,crit,IF(OR(crit="foo",crit="bar"),ABS(val),0))))
SG
Good question. FILTER lets you use * and gives no error or warning but not with + . It does not appear to work with an actual boolean expression that uses OR (or I didn't do it right). You could separate them, doing a FILTER for "foo" and a FILTER for "bar", with both being in the SUM. There may be a better way. I am new to these new functions and still learning.
In addition to the two above, here's are two more ways, each of which uses filter:
=SUM(FILTER(ABS(A), MAP(B, LAMBDA(crit, IF(OR(crit="foo", crit="bar"), 1, 0))), 0))
=SUM(FILTER(ABS(A), MAP(B, LAMBDA(crit, ISNUMBER(MATCH(crit, {"foo","bar"},0)))), 0))
SG
papalapapp wrote:
I have multiple AND and OR conditions that I need to pack in to one cell.
Not only filtering for certain words but also within a certain date range. For example: What is the sum of type A and B between date X and Y.
Here are two possibilities:
=SUM(MAP(Data::A, Data::B, Data::C, LAMBDA(date, type, val, IF(AND(date≥DATE(2025,4,28), date≤DATE(2025,5,20), ISNUMBER(MATCH(type, {"A","B"},0))), val, 0))))
=SUM(FILTER(Data::C, MAP(Data::A, Data::B, LAMBDA(date, type, IF(AND(date≥DATE(2025,4,28), date≤DATE(2025,5,20), ISNUMBER(MATCH(type, {"A","B"},0))), 1, 0))), 0))
There is a learning curve for these new functions but they sure are powerful!
SG
Oh wow thank you for these suggestions. I must admit that I have not made myself familiar with variables. I'll do some reading in the Numbers help and try them out.
Usually I am in favor of the most simple solution like splitting it in to their own SUMIFS. But in this case I have multiple AND and OR conditions that I need to pack in to one cell.
Not only filtering for certain words but also within a certain date range. For example: What is the sum of type A and B between date X and Y.
Thank you. The learing curve is quite steep. There is nothing self-explanatory here. The way the funcions are named and the dynamic info at the bottom of the window isn't particularly helpful for me. When I check the parts of the formula for the results of the individual steps, for example MAP, then there is the message "...needs more rows to spill the results...". Then, when placing the formula in the first row, it says something like "...cells must be empty to spill the results...". Quite frustrating when I want a result just for that cell.
However, I think I know what you did here in the lower example. You replaced the direct criteria for the FILTER function with MAP, which is supposed to return 1 or 0 depending on the checked date or type. This avoids the blue triangle that complains about not having boolean values. Good move. The ISNUMBER part also works as a regular OR function.
For the upper example SUM( MAP( ... there is something I don't understand. MAP does "map" values in an array to new values, right? This formula then would map the array of 3 columns A, B and C to "val" if they meet the criteria from the IF function. In theory that shouldn't be possible because neither of the individual values meets all three criteria (date and type). That means, MAP outputs a single-column array with the "val" for each row where Date and Type meet the IF-criteria...?
Another question regarding MAP( LAMBDA(... : The variables in LAMBDA are "mapped" to the initial arrays that are defined in MAP. Do I understand that correctly?
[Edited by Moderator]
The explanation here may help:
Terse, but it should clarify, at least a little, the basic idea.
SG
How to avoid or remove the blue triangle saying that the formula is using boolean values...?
