Simplify Numbers multiple if formula

Your results "31", "32" etc should not be in quotes but that is a side point to the answer to your question. It makes them strings. As numeric characters, Numbers turns them into numbers for the additions you are making but, in general, using + on a string is an error.

I can give a close answer but probably not a full answer. Not trying to muddy the waters but the problem is not fully defined.

• I assume you want the answer to be a number, not a string of numeric digits. There is a difference.
• I don't know if you really mean SUM or if you want COUNT or COUNTIF. I used SUM but if you put a number larger than 1 in any of those cells it will sum that number. COUNT will count how many cells have anything numeric in them. COUNTIF can count the number cells that have a 1, ignoring anything else. Or you could use checkboxes and then COUNTIF can count the number of checked boxes (cell = true).
• I don't know what to do if SUM(N53:R53) is 0. The result will be 0 in the formula below.
• In the formula below, if SUM(N53:R53)=4 and SUM(T53:U53)=0 the answer will be 4 (as in your screenshot) but it will also be 4 if SUM(N53:R53)=0 and SUM(T53:U53)=4

Anyway, maybe this formula will get you on a different track to solving your problem:

=IF(SUM(T53:U53)>0; SUM(T53:U53)+10×SUM(N53:R53);SUM(N53:R53))

You should also look at the IFS function if you need addition conditions in this formula, like for when SUM(N53:R53)=0.

(Edited to change commas to semicolons in the formula)

Jp-design wrote:

Hi SG

I was just cheking everithing and I have to make a correction

22 is a valid value too!

From your latest screenshot it seems as if you want a column that contains a code if the value is valid, and 0 if it is not.

If so, you can try something like this:

=IF(VALUE(V2&W2)<12;"0";IF(V2=0;"";V2)&IF(W2>0;W2;""))

If you don't like having the extra columns then in the formula you can simply substitute SUM(N2:R2) where you see V2 and SUM(T2:U2) where you see W2.

SG

=IFS(SUM(N53:R53)=0; ""; AND(SUM(N53:R53)=1;SUM(T53:U53)<2);"";SUM(T53:U53)=0;SUM(N53:R53);TRUE;SUM(N53:R53)&SUM(T53:U53))&""

Edit: I again forgot to use semicolons in the formula

The results are text.

alternate formula

=IFS(10×SUM(N53:R53)+SUM(T53:U53)<12,"",SUM(T53:U53)=0,SUM(N53:R53), TRUE,10×SUM(N53:R53)+SUM(T53:U53))

results are numbers, not text

Both your screenshot and your formula appear to be truncated, making it very hard to follow what you are trying to do. Could you repost? It would be helpful to include the column letters at the top and row numbers at the left in your screenshot.

Also what are you trying to "validate" and what calculations are you doing to validate? All we can see is a partial formula and two (possibly three) cells with a green background somewhere in some Numbers table.

SG

If I understand you correctly then you could try something like this:

In the example I have in X2, filled down, this formula:

=NOT(OR(V2=0;AND(V2=1;W2<2);AND(V2=2;W2=2)))

If you don't like having the extra columns then you could simply substitute SUM(N2:R2) for V2 and SUM(T2:U2) for W2 in this formula.

SG

Jp-design wrote:

the goal I am looking for

The exact goal still remains a little bit of a mystery.😀

So whether we have been able to help (or not) remains a mystery too.

Good luck with the project!

SG

SG

While they are not necessary and it can be done without them (in contrast to some problems that require extra columns of formulas), it does make the formulas look cleaner. It also saves calculating the same sums repeatedly in the formula.

Are you looking to do something like this?

SG

Or slightly shorter:

=OR(AND(V2=1;W2>1);AND(V2=2;W2<>2);V2>2)

SG