Subtotal function 1 that excludes "#VALUE!"

  • Thread starter Thread starter JAbels001
  • Start date Start date
Please place the question in the body of the email, all I can read with my
browser is the following when I hit reply:

"An error occurred while sending your post
 
This happens when there is no text in the body (as in this post).

We have been requesting all users to describe the problem in the text of the
post but not everyone does that... :-(
 
Sorry, I was trying to reply and describe the problem but it wouldnt let me
until someone responded.

I'm trying to calculate a subtotal formula b/c the data I'm using can be
filtered and I want it to update whenever it is changed. The problem is that
some of the values are "#VALUE!" which results in the subtotal formula coming
back as #VALUE!. Please help...
 
Get rid of the errors in the cells being subtotalled.

Trap for it in your formulas.

Post an example of two of the cells that return #VALUE! that are causing
the error in the Subtotal.


Gord Dibben MS Excel MVP
 
You must be getting #N/A from your formula...

Wrap your formula in a IF and ISNA like this
=IF(ISNA(your formula here),0,your formula here))

If you don't want 0 you can replace it with "" (2 double quotes)
 
Unfortunately that is a harder task than getting the subtotal to exclude
them. Is there anyway to have a cell that only shows the value of another
cell just what is in that cell? For example on my one tab I have a formula
that reads "=April!I1", however if I1 on tab "April" is blank then it shows
"0" not blank. I would like it to show the true contents.
 
Use
=IF(April!I1="","",April!I1)


JAbels001 said:
Unfortunately that is a harder task than getting the subtotal to exclude
them. Is there anyway to have a cell that only shows the value of another
cell just what is in that cell? For example on my one tab I have a formula
that reads "=April!I1", however if I1 on tab "April" is blank then it shows
"0" not blank. I would like it to show the true contents.
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtotal Avg Exclude "0" Values 3
Subtotals 2
Conditional SUBTOTAL in XL210 Table 7
SUBTOTAL function 3
Sum a Filtered List excluding Negative Numbers 6
Exclude a row from a SUM 2
Subtotal placement issue 2
count specific subtotal values only 9

Back
Top