|
Subtotal
Hi,
Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. A 1 4,084.20 2 #N/A 3 450.00 4 3,965.00 5 #N/A 6 #N/A ----------------- 8,499.20 ( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 1,037.20 11 750.00 12 #N/A 13 16,200.00 ---------------------- 17,987.20 ( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- 26,486.60 ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard |
Subtotal
You could use an array formula:
=sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. A 1 4,084.20 2 #N/A 3 450.00 4 3,965.00 5 #N/A 6 #N/A ----------------- 8,499.20 ( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 1,037.20 11 750.00 12 #N/A 13 16,200.00 ---------------------- 17,987.20 ( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- 26,486.60 ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson |
Subtotal
On May 19, 1:20 am, Dave Peterson wrote:
You could use an array formula: =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007.. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. A 1 4,084.20 2 #N/A 3 450.00 4 3,965.00 5 #N/A 6 #N/A ----------------- 8,499.20 ( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 1,037.20 11 750.00 12 #N/A 13 16,200.00 ---------------------- 17,987.20 ( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- 26,486.60 ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Thanks for your suggestion and it works in above scenario However, how can array formula apply into the following scenario 2 after doing data sorting and running subtotal function so that it will ignore cell error while adding up the values in a column ? E.g. After sorting out the data in numerical order and running subtotal function, the subtotal shows #N/A A B 1 1 4,084.20 2 1 #N/A 3 1 450.00 4 1 3,965.00 5 1 #N/A 6 #N/A 7 1 Total #N/A --------„³ 8,499.20 8 2 1,037.20 9 2 750.00 10 2 #N/A 11 2 16,200.00 12 2 Total #N/A ------„³ 17,987.20 13 Grand Total #N/A ------„³ 26,486.60 Thanks again Regards Len |
Subtotal
If those cells showing #n/a contain formulas, I'd change the formula to return
text--not an error. Then the =subtotal() would work ok. Len wrote: On May 19, 1:20 am, Dave Peterson wrote: You could use an array formula: =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. A 1 4,084.20 2 #N/A 3 450.00 4 3,965.00 5 #N/A 6 #N/A ----------------- 8,499.20 ( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 1,037.20 11 750.00 12 #N/A 13 16,200.00 ---------------------- 17,987.20 ( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- 26,486.60 ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Thanks for your suggestion and it works in above scenario However, how can array formula apply into the following scenario 2 after doing data sorting and running subtotal function so that it will ignore cell error while adding up the values in a column ? E.g. After sorting out the data in numerical order and running subtotal function, the subtotal shows #N/A A B 1 1 4,084.20 2 1 #N/A 3 1 450.00 4 1 3,965.00 5 1 #N/A 6 #N/A 7 1 Total #N/A --------„³ 8,499.20 8 2 1,037.20 9 2 750.00 10 2 #N/A 11 2 16,200.00 12 2 Total #N/A ------„³ 17,987.20 13 Grand Total #N/A ------„³ 26,486.60 Thanks again Regards Len -- Dave Peterson |
Subtotal
On May 19, 11:01*pm, Dave Peterson wrote:
If those cells showing #n/a contain formulas, I'd change the formula to return text--not an error. *Then the =subtotal() would work ok. Len wrote: On May 19, 1:20 am, Dave Peterson wrote: You could use an array formula: =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. *Hit ctrl-shift-enter instead of enter. *If you do it correctly, excel will wrap curly brackets {} around your formula. *(don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. * * * *A 1 4,084.20 2 * *#N/A 3 * *450.00 4 *3,965.00 5 * *#N/A 6 * *#N/A ----------------- * *8,499.20 *( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 * *1,037.20 11 * * * 750.00 12 * * #N/A 13 * 16,200.00 ---------------------- * * *17,987.20 *( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- * * 26,486.60 * ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Thanks for your suggestion and it works in above scenario However, how can array formula apply into the following scenario 2 after doing data sorting and running subtotal function so that it will ignore cell error while adding up the values in a column ? E.g. After sorting out the data in numerical order and running subtotal function, the subtotal shows #N/A * * * * A * * * * * * * * * * * * * * * B 1 * * *1 * * * * * * * * * * * *4,084.20 2 * * *1 * * * * * * * * * * * * #N/A 3 * * *1 * * * * * * * * * * * * * 450.00 4 * * *1 * * * * * * * * * * * *3,965.00 5 * * *1 * * * * * * * * * * * * *#N/A 6 * * * * * * * * * * * * #N/A 7 * * *1 Total * * * * * *#N/A *--------„³ 8,499..20 8 * * *2 * * * * * * * * * * * *1,037.20 9 * * *2 * * * * * * * * * * * * * 750.00 10 * *2 * * * * * * * * * #N/A 11 * *2 * * * * * * * * * * * * * 16,200.00 12 * *2 Total * * * * * * #N/A ------„³ * 17,987.20 13 * *Grand Total * * * * * #N/A *------„³ *26,486..60 Thanks again Regards Len -- Dave Peterson- Hide quoted text - - Show quoted text - In this case, before applying subtotal function, I 've tried to use replace function to change error cells "#N/A" contains formula into text or value cells "0.00" but it fails, is there any other way ? Regards Len |
Subtotal
What was the formula you used?
Len wrote: <<snipped In this case, before applying subtotal function, I 've tried to use replace function to change error cells "#N/A" contains formula into text or value cells "0.00" but it fails, is there any other way ? Regards Len -- Dave Peterson |
Subtotal
On May 20, 10:36 am, Len wrote:
On May 19, 11:01 pm, Dave Peterson wrote: If those cells showing #n/a contain formulas, I'd change the formula to return text--not an error. Then the =subtotal() would work ok. Len wrote: On May 19, 1:20 am, Dave Peterson wrote: You could use an array formula: =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. A 1 4,084.20 2 #N/A 3 450.00 4 3,965.00 5 #N/A 6 #N/A ----------------- 8,499.20 ( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 1,037.20 11 750.00 12 #N/A 13 16,200.00 ---------------------- 17,987.20 ( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- 26,486.60 ( Note : excel formula to add up subtotal of 8,499..20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Thanks for your suggestion and it works in above scenario However, how can array formula apply into the following scenario 2 after doing data sorting and running subtotal function so that it will ignore cell error while adding up the values in a column ? E.g. After sorting out the data in numerical order and running subtotal function, the subtotal shows #N/A A B 1 1 4,084.20 2 1 #N/A 3 1 450.00 4 1 3,965.00 5 1 #N/A 6 #N/A 7 1 Total #N/A --------„³ 8,499.20 8 2 1,037.20 9 2 750.00 10 2 #N/A 11 2 16,200.00 12 2 Total #N/A ------„³ 17,987.20 13 Grand Total #N/A ------„³ 26,486.60 Thanks again Regards Len -- Dave Peterson- Hide quoted text - - Show quoted text - In this case, before applying subtotal function, I 've tried to use replace function to change error cells "#N/A" contains formula into text or value cells "0.00" but it fails, is there any other way ? Regards Len Sounds like you might be using search and replace. What you want is something like =IF(ISNA(<your calculation here that sometimes results in NA),0,<your calculation here that sometimes results in NA) more concrete example: =IF(isna(match("Dogs",F:F,0)),0,match("Dogs",F:F,0 )) |
Subtotal
On May 21, 12:47*am, Dave Peterson wrote:
What was the formula you used? Len wrote: <<snipped In this case, before applying subtotal function, I 've tried to use replace function to change error cells "#N/A" contains formula into text or value cells "0.00" but it fails, is there any other way ? Regards Len -- Dave Peterson "Ctrl+H" with find what "#N/A" and replace with "0.00" |
Subtotal
On May 21, 2:58*am, wrote:
On May 20, 10:36 am, Len wrote: On May 19, 11:01 pm, Dave Peterson wrote: If those cells showing #n/a contain formulas, I'd change the formula to return text--not an error. *Then the =subtotal() would work ok. Len wrote: On May 19, 1:20 am, Dave Peterson wrote: You could use an array formula: =sum(if(isnumber(a1:a10),a1:a10)) This is an array formula. *Hit ctrl-shift-enter instead of enter.. *If you do it correctly, excel will wrap curly brackets {} around your formula. *(don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Len wrote: Hi, Is there any excel function for subtotal to ignore cell error while adding up the values in a column ? E.g. * * * *A 1 4,084.20 2 * *#N/A 3 * *450.00 4 *3,965.00 5 * *#N/A 6 * *#N/A ----------------- * *8,499.20 *( Note : excel formula to add up the column A from A1 to A6 to arrive the subtotal of 8,499.20 ) 10 * *1,037.20 11 * * * 750.00 12 * * #N/A 13 * 16,200.00 ---------------------- * * *17,987.20 *( Note : excel formula to add up the column A from A10 to A13 to arrive the subtotal of 17,987.20 ) ---------------------- * * 26,486.60 * ( Note : excel formula to add up subtotal of 8,499.20 and 17,987.20, grandtotal shows 26,486.60 ) =========== Please help, thanks Regards Lenard -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Thanks for your suggestion and it works in above scenario However, how can array formula apply into the following scenario 2 after doing data sorting and running subtotal function so that it will ignore cell error while adding up the values in a column ? E.g. After sorting out the data in numerical order and running subtotal function, the subtotal shows #N/A * * * * A * * * * * * * * * * * * * * * B 1 * * *1 * * * * * * * * * * * *4,084.20 2 * * *1 * * * * * * * * * * * * #N/A 3 * * *1 * * * * * * * * * * * * * 450.00 4 * * *1 * * * * * * * * * * * *3,965.00 5 * * *1 * * * * * * * * * * * * *#N/A 6 * * * * * * * * * * * * #N/A 7 * * *1 Total * * * * * *#N/A *--------„³ 8,499.20 8 * * *2 * * * * * * * * * * * *1,037.20 9 * * *2 * * * * * * * * * * * * * 750.00 10 * *2 * * * * * * * * * #N/A 11 * *2 * * * * * * * * * * * * * 16,200.00 12 * *2 Total * * * * * * #N/A ------„³ * 17,987.20 13 * *Grand Total * * * * * #N/A *------„³ *26,486.60 Thanks again Regards Len -- Dave Peterson- Hide quoted text - - Show quoted text - In this case, before applying subtotal function, I 've tried to use replace function to change error cells "#N/A" contains formula into text or value cells "0.00" but it fails, is there any other way ? Regards Len Sounds like you might be using search and replace. What you want is something like =IF(ISNA(<your calculation here that sometimes results in NA),0,<your calculation here that sometimes results in NA) more concrete example: =IF(isna(match("Dogs",F:F,0)),0,match("Dogs",F:F,0 ))- Hide quoted text - - Show quoted text - Hi, Appreciate your help, how to apply your excel formula into the above scenario 2 with subtotal function Regards Lenard |
Subtotal
If the cell contains a formula that returns that #n/a, then edit|replace
(ctrl-h) won't help. If you want to change all the formulas that return #n/a, you could try this: Select the range Edit|goto (or ctrl-g) Special Formulas, but only leave Errors checked (uncheck Numbers, Text, Logicals) Click Ok. Now only the cells that have formulas that return errors are selected. type 0 and hit ctrl-enter to replace those formulas with 0. Len wrote: On May 21, 12:47 am, Dave Peterson wrote: What was the formula you used? <<snipped "Ctrl+H" with find what "#N/A" and replace with "0.00" -- Dave Peterson |
Subtotal
On May 21, 10:15*am, Dave Peterson wrote:
If the cell contains a formula that returns that #n/a, then edit|replace (ctrl-h) won't help. If you want to change all the formulas that return #n/a, you could try this: Select the range Edit|goto (or ctrl-g) Special Formulas, but only leave Errors checked (uncheck Numbers, Text, Logicals) Click Ok. Now only the cells that have formulas that return errors are selected. type 0 and hit ctrl-enter to replace those formulas with 0. Len wrote: On May 21, 12:47 am, Dave Peterson wrote: What was the formula you used? <<snipped "Ctrl+H" with find what "#N/A" and replace with "0.00" -- Dave Peterson Great !, it works.......... thanks Regards Len |
| All times are GMT +1. The time now is 08:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com