How do I add up cells that are autofiltered?
 

I am trying to sum up a column a data which includes numbers and the #N/A
indicator. These numbers are from a vlookup formula. The column is in a
spreadsheet that's been autofiltered for specific text in another column. How
do I add up the numberd derived from the vlookup formula in the column with
the condition that #N/A does not get included in that equation. The #N/A
totally messes up the sum equation.

Thank you,

Paul

How do I add up cells that are autofiltered?
 

Think you could use something like this in say, C1:
=SUMIF(B:B,"<#N/A")
where col B contains the vlookup returns which may include #N/A returns
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PayPaul" wrote:
I am trying to sum up a column a data which includes numbers and the #N/A
indicator. These numbers are from a vlookup formula. The column is in a
spreadsheet that's been autofiltered for specific text in another column. How
do I add up the numberd derived from the vlookup formula in the column with
the condition that #N/A does not get included in that equation. The #N/A
totally messes up the sum equation.

Thank you,

Paul

How do I add up cells that are autofiltered?
 

Beautiful. I knew there was a criteria operand missing. The ones they never
tell you about.
One thing I've found it's almost impossible to find the #N/A when it's
associated with a formula. It's equally impossible to sum up autofiltered
data with # N/A in it.

Thank you,

Paul

"Max" wrote:

Think you could use something like this in say, C1:
=SUMIF(B:B,"<#N/A")
where col B contains the vlookup returns which may include #N/A returns
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PayPaul" wrote:
I am trying to sum up a column a data which includes numbers and the #N/A
indicator. These numbers are from a vlookup formula. The column is in a
spreadsheet that's been autofiltered for specific text in another column. How
do I add up the numberd derived from the vlookup formula in the column with
the condition that #N/A does not get included in that equation. The #N/A
totally messes up the sum equation.

Thank you,

Paul

How do I add up cells that are autofiltered?
 

Max has given an excellent answer but you did not say if the sum was to be
the sum of the visible numbers (those selected by the filter)

To get the filtered sum we use the SUBTOTAL function as in
=SUBTOTAL(109,H2:H50)

However this will fail with the N/A present. A work around would be to redo
the VLOOKUP so that it returns 0 rather than #N/A.
=IF(ISNA(your-vlookup),0,your-vlookup)
Then you can 'cheat' to make the zero look like N/A with Custom formatting
0;-0;"N/A"

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"PayPaul" wrote in message
...
I am trying to sum up a column a data which includes numbers and the #N/A
indicator. These numbers are from a vlookup formula. The column is in a
spreadsheet that's been autofiltered for specific text in another column.
How
do I add up the numberd derived from the vlookup formula in the column
with
the condition that #N/A does not get included in that equation. The #N/A
totally messes up the sum equation.

Thank you,

Paul

How do I add up cells that are autofiltered?
 

"=IF(ISNA" is what I would add to the beginning of the formala and then
follow up with my vlookup sequence. I want to make sure I understand the
formula syntax that you are amending in your answer.
Yes, I did discover that my SUM figures seem to include numbers from cells
that are hidden by the autofiltered sheet. If you can please clarify the
syntax that would be appreciated.

Thank you.

Paul

"Bernard Liengme" wrote:

Max has given an excellent answer but you did not say if the sum was to be
the sum of the visible numbers (those selected by the filter)

To get the filtered sum we use the SUBTOTAL function as in
=SUBTOTAL(109,H2:H50)

However this will fail with the N/A present. A work around would be to redo
the VLOOKUP so that it returns 0 rather than #N/A.
=IF(ISNA(your-vlookup),0,your-vlookup)
Then you can 'cheat' to make the zero look like N/A with Custom formatting
0;-0;"N/A"

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"PayPaul" wrote in message
...
I am trying to sum up a column a data which includes numbers and the #N/A
indicator. These numbers are from a vlookup formula. The column is in a
spreadsheet that's been autofiltered for specific text in another column.
How
do I add up the numberd derived from the vlookup formula in the column
with
the condition that #N/A does not get included in that equation. The #N/A
totally messes up the sum equation.

Thank you,

Paul