Summing a range + other cell with N/A values

Shane

Board Regular
Joined
Apr 8, 2002
Messages
51
Hi! Following from previous problem that has been solved, I tried to apply the following solutions that work fine on the range M2:P2 but don't work when I include I2 in the range to be summed:

{=SUM(IF(ISNUMBER(M2:P2,I2),M2:P2,I2),1)}

=SUMIF(M2:P2,I2,"<>#N/A")

What should I do to get around this problem?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Don't worry, figured it out:

=SUMIF(M2:P2,"<>#N/A")+SUMIF(I2:I2,"<>#N/A")

I'm still curious as to why you need to split the ranges out though?
 
Upvote 0
On 2002-04-09 09:04, Shane wrote:
Don't worry, figured it out:

=SUMIF(M2:P2,"<>#N/A")+SUMIF(I2:I2,"<>#N/A")

I'm still curious as to why you need to split the ranges out though?

You 2nd SUMIF is redundant. Use...

=SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)
 
Upvote 0
On 2002-04-09 09:00, Shane wrote:
Hi! Following from previous problem that has been solved, I tried to apply the following solutions that work fine on the range M2:P2 but don't work when I include I2 in the range to be summed:

{=SUM(IF(ISNUMBER(M2:P2,I2),M2:P2,I2),1)}

=SUMIF(M2:P2,I2,"<>#N/A")

What should I do to get around this problem?

Hi Shane:
I am not sure if I understand fully what you are trying to do -- but for summing just numbers for l2:p2,
L2 ... 6
M2 ... 2
N2 ... 3
O2 ... a
P2 ... 5
using the following formula

=SUM(IF(ISNUMBER(L2:P2),L2:P2))

results in 16 -- which is correct

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!
=SUM(IF(ISNUMBER(L2:P2),L2:P2))
 
Upvote 0
Mark, I have tried your formula. It returns an incorrect answer. The formula I used with the 2 SUMIF functions returned the correct result so I can only assume that the second SUMIF statement is not redundant?
 
Upvote 0
Hi Shane:
In my first post I missed the fact that you did have some #N/A in your data. I still might be missing something else ... but
How about the single sum formula:

=sumif(L2:p2,"<>#N/A",L2:P2)

for me this single formula gives the right result.

HTH
This message was edited by Yogi Anand on 2002-04-09 10:01
 
Upvote 0
On 2002-04-09 09:15, Shane wrote:
Mark, I have tried your formula. It returns an incorrect answer. The formula I used with the 2 SUMIF functions returned the correct result so I can only assume that the second SUMIF statement is not redundant?

Then your assumption would be incorrect. Using SUMIF with a single cell, and then adding (with + operator) the results is redundant. =1+SUM(1) is the same as =1+1; however, the former uses a superfluous function call.

I can't diagnose your problem because I don't know what constitutes an "incorrect answer"?
This message was edited by Mark W. on 2002-04-09 10:10
 
Upvote 0
Hi Mark. I have revisited and your solution does indeed work (I obviously mistyped something).

Although the other solution does give the same result as your suggested formula, I agree with your assertion that my formula was overkill. I now understand the neatest solution - many thanks!

Yogi, thanks for your input too.
 
Upvote 0
Hi Shane and Mark:
Referring to Mark's compact solution in ...
=SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)

If we wanted to make it still more compact, use

=sumif(M2:P2,"<>#N/A")+N(L2)
 
Upvote 0
On 2002-04-09 21:05, Yogi Anand wrote:
Hi Shane and Mark:
Referring to Mark's compact solution in ...
=SUMIF(M2:P2,"<>#N/A")+IF(ISNUMBER(I2),I2)

If we wanted to make it still more compact, use

=sumif(M2:P2,"<>#N/A")+N(L2)

Yogi,

When L2=#N/A, N(L2) will return #N/A, so will the modified formula.The IF(ISNUMBER(I2),I2) is the only way to eliminate an error value L2 might come to have.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top