Problem with addition of columns

[sharshra]

I have to add 2 columns. It is simple to add these 2 columns using +. But in the actual scenario, blank cell is shown as blank if there was an error using IFERROR. So, I can´t add columns using +.

So, I´m using IFNUMBER to add only if cell contains numbers. It works well when applied on each cell (refer column D). But, when I apply the same on a range, when both cells contains numbers, it is counting only the first cell & not returning the sum (refer column E). Refer the cells highlighted in yellow. Not sure what is the problem. Can the experts help please?

excel problems.xlsx
	B	C	D	E
56	1		1	1
57	4	8	12	4
58	7		7	7
59		9	9	9
60	11	2	13	11
61		76	76	76
62		98	98	98
63	34	3	37	34
merge tables

Cell Formulas
Range	Formula
E56:E63	E56	=IF(AND(ISNUMBER(B56:B63),ISNUMBER(C56:C63)),B56:B63+C56:C63,IF(ISNUMBER(B56:B63),B56:B63,C56:C63))
D56:D63	D56	=IF(AND(ISNUMBER(B56),ISNUMBER(C56)),B56+C56,IF(ISNUMBER(B56),B56,C56))
Dynamic array formulas.

 

[sharshra]

I have resolved it as shown in column D. But would like to know what was the problem before. Can the experts please advise?

excel problems.xlsx
	B	C	D	E
56	1		1	1
57	4	8	12	4
58	7		7	7
59		9	9	9
60	11	2	13	11
61		76	76	76
62		98	98	98
63	34	3	37	34
merge tables

Cell Formulas
Range	Formula
D56:D63	D56	=IF(B56:B63="",0,B56:B63)+IF(C56:C63="",0,C56:C63)
E56:E63	E56	=IF(AND(ISNUMBER(B56:B63),ISNUMBER(C56:C63)),B56:B63+C56:C63,IF(ISNUMBER(B56:B63),B56:B63,C56:C63))
Dynamic array formulas.

 

[Peter_SSs]

I have resolved it as shown in column D.

Another way

24 10 27.xlsm
	B	C	D
56	1		1
57	4	8	12
58	7		7
59		9	9
60	11	2	13
61		76	76
62		98	98
63	34	3	37
Sum rows

Cell Formulas
Range	Formula
D56:D63	D56	=BYROW(B56:C63,LAMBDA(r,SUM(r)))
Dynamic array formulas.

 

[sharshra]

Thanks, @Peter_SSs

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
The gist of it is that SUM automatically ignores text values - so no need for individual tests.

 

[Alex Blakenburg]

But would like to know what was the problem before.

I know you now have a solution but to answer your question.
AND, OR etc don't evaluate the whole range. This is why in other formulas such as the Filter function you will see multiplication "*" used for AND and plus "+" for OR.
In your initial formula it would become this:

=IF( (ISNUMBER(B56:B63) * ISNUMBER(C56:C63) ), B56:B63+C56:C63, IF(ISNUMBER(B56:B63), B56:B63, C56:C63))