Subtotal

[Len]

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]

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.

Post by Len
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

[Len]

Post by Dave Peterson
=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.

Post by Len
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]

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.

Post by Len

Post by Dave Peterson
=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.

Post by Len
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

[Len]

Post by Dave Peterson
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.

Post by Len

Post by Dave Peterson
=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.

Post by Len
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

[Dave Peterson]

What was the formula you used?

Len wrote:
<<snipped>>

Post by Len
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

[Len]

Post by Dave Peterson
What was the formula you used?
<<snipped>>

Post by Len
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"

[Dave Peterson]

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.

Post by Len

Post by Dave Peterson
What was the formula you used?

<<snipped>>

Post by Len
"Ctrl+H" with find what "#N/A" and replace with "0.00"

[Len]

Post by Dave Peterson
If the cell contains a formula that returns that #n/a, then edit|replace
(ctrl-h) won't help.
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.

Post by Len

Post by Dave Peterson
What was the formula you used?

<<snipped>>

Post by Len
"Ctrl+H" with find what "#N/A" and replace with "0.00"

--
Dave Peterson

Great !, it works.......... thanks

Regards
Len

[c***@gmail.com]

Post by Len

Post by Dave Peterson
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.

Post by Len

Post by Dave Peterson
=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.

Post by Len
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))

[Len]

Post by c***@gmail.com

Post by Len

Post by Dave Peterson
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.

Post by Len

Post by Dave Peterson
=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.

Post by Len
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>)
=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