Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 196
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default 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






  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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






  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro unouwanme Excel Discussion (Misc queries) 9 August 31st 06 09:38 PM
Copying a column of single cells into a column of merged cells [email protected] Excel Discussion (Misc queries) 1 August 16th 06 01:18 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM


All times are GMT +1. The time now is 08:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"