Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Copying a column of single cells into a column of merged cells | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |