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
|