How to sum formulas that returns a #N/A

[cc9083]

Hi there. I have 3 separate columns (Column H, J & K) that has the "index and match" formulas going vertically downwards. I've created another Column L to sum the row results of Column H, J & K respectively. However, since the "index and match" formulas in Column H, J & K always return #N/A results it is certain that I'll have the same #N/A for my sum in Column L. Even when 1 of the 3 columns has valid data (a figure) the sum in Column L will return the same #N/A.

I have searched for a solution and this was the formula that was recommended in another post but it works only for consequetive cells. How do I tweak in order for it to work on interval cells.

=SUM(IF(ISNA(C4:C11),0,C4:C11)) - Needs to be entered with Ctrl + Shift+ Enter

Your immediate assistance is much appreciated. Thanks in advance.

 

[Krishnakumar]

What's the range to sum ?

Is it what you are after?

=SUM(SUMIF(H15:I15,{">0","<0"},H15:I15),SUMIF(K15,{">0","<0"}))

adjust the range.

 

[cc9083]

Hi Kris,

Sorry there is some typos in my request. Corrected in red.

Hi there. I have 3 separate columns (Column H, J & L) that has the "index and match" formulas going vertically downwards. I've created another Column M to sum the row results of Column H, J & L respectively. However, since the "index and match" formulas in Column H, J & L always return #N/A results it is certain that I'll have the same #N/A for my sum in Column M. Even when 1 of the 3 columns has valid data (a figure) the sum in Column M will return the same #N/A.

I have searched for a solution and this was the formula that was recommended in another post but it works only for consequetive cells. How do I tweak in order for it to work on interval cells.

=SUM(IF(ISNA(C4:C11),0,C4:C11)) - Needs to be entered with Ctrl + Shift+ Enter

Your immediate assistance is much appreciated. Thanks in advance.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(H4:L100),H4:L100))-SUM(I4:I100,K4:K100)

Or:

M4:

=SUM(SUMIF(H4,">0"),SUMIF(J4,">0"),SUMIF(L4,">0"))

Then run an ordinary Sum on M...

=SUM(M4:M100)

 

[PATSYS]

In cell M2,

=SUM(IF(ISNA(H2:L2)=FALSE,(H2:L2)))

Confirm with CTRL+SHIFT+ENTER

Copy down.