Sum some cells in column but not #n/a errors

[Guest]

This has been baffling me for hours.

How can I get Excel to sum a selection of cells, but ignore #N/A errors?

I have a column of data, returning profit values for a particular product
and percentages of that profit to a supplier each month

I want to sum the profit and the percentages separately, but as the data is
also used for charts, I don’t want it to go to zero if I don’t have the
information. I either have# n/a errors occurring if not all the data is in,
or I have charts that drop to zero and I don’t really want either.

Eg:


Oct-06

Client A profit £1545.55
Share to partner 1 £154.56
Share to partner 2 £193.19
Profit £1197.80

Is this possible?

Thanks

 

[RichardSchollar]

You can use an array formula:

=SUM(IF(ISNUMBER(YourRange),YourRange))

Confirmed with Ctrl+Shift+Enter (Excel will surround the formula with
curly braces {} if entered correctly). This will exclude non-numeric
values from your sum range.

Hope this helps!

Richard

 

[Bob Phillips]

=SUMIF(A2:A20,"<>#N/A")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks for this, Bob.

Is there a way I can get the same result if i only want some of the cells
from A2 to A20?

Thanks

 

[Guest]

Thanks Richard

Forgive me if I'm being dim, but how do I specify the range of cells?

Thanks

 

[Bob Phillips]

As long as they are contiguous, change the range.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

How woudl you deal with it if they were not contiguous (and weren't at a
regular interval, either)

Thanks

 

[Bob Phillips]

Something like

=SUMPRODUCT(SUMIF(INDIRECT({"A2","A4","A7"}),"<>#N/A"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)