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

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

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

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

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

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

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 ))

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"

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

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

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