Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting copied AutoFiltered cells in Excel2007 | Excel Discussion (Misc queries) | |||
Can you edit your data set while it is autofiltered? | Excel Discussion (Misc queries) | |||
Ranking With Sum Product Formulas over Autofiltered Cells | Excel Discussion (Misc queries) | |||
autofiltered file | Excel Discussion (Misc queries) | |||
Copying to an autofiltered list | Excel Discussion (Misc queries) |