# How to sum formulas that returns a #N/A

#### cc9083

##### New Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Krishnakumar

##### Well-known Member
What's the range to sum ?

Is it what you are after?

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

#### cc9083

##### New Member
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

##### MrExcel MVP
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)

Last edited:

#### PATSYS

##### Well-known Member
In cell M2,

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

Confirm with CTRL+SHIFT+ENTER

Copy down.

Replies
0
Views
203
Replies
2
Views
108
Replies
2
Views
179
Replies
3
Views
138
Replies
0
Views
109

1,191,701
Messages
5,988,176
Members
440,135
Latest member
DCDavid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back