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

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 dont want it to go to zero if I dont 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 dont 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

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

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


Unicorn wrote:

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

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

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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

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

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


"Bob Phillips" wrote:

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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

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

Thanks Richard

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

Thanks



"RichardSchollar" wrote:

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


Unicorn wrote:

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

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

As long as they are contiguous, change the range.

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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


"Bob Phillips" wrote:

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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

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

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

Thanks

"Bob Phillips" wrote:

As long as they are contiguous, change the range.

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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


"Bob Phillips" wrote:

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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

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

Something like

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
How woudl you deal with it if they were not contiguous (and weren't at a
regular interval, either)

Thanks

"Bob Phillips" wrote:

As long as they are contiguous, change the range.

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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


"Bob Phillips" wrote:

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

--

HTH

Bob Phillips

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

"Unicorn" wrote in message
...
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