Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sum the column with the error cells
I am trying to add the total for one column in which some cells have the
#REF! sign. My formula is: =SUMIF(Data!A1:Data!A100,"<#VALUE!") but the output is still #REF! and cannot display the value total. Does my formula miss any? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sum the column with the error cells
Your sheet reference is invalid (you truly do have a reference error).
=SUMIF(Data!A1:A100,"<#VALUE!") Of note, why are you checking for "#VALUE!"? should it be searching for the "#REF!"? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "David" wrote: I am trying to add the total for one column in which some cells have the #REF! sign. My formula is: =SUMIF(Data!A1:Data!A100,"<#VALUE!") but the output is still #REF! and cannot display the value total. Does my formula miss any? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sum the column with the error cells
Thanks, Luke. I thought the <#VALUE! means check out for all criterias
except the NOT VALUE data. How about to include the #NUM!? "Luke M" wrote: Your sheet reference is invalid (you truly do have a reference error). =SUMIF(Data!A1:A100,"<#VALUE!") Of note, why are you checking for "#VALUE!"? should it be searching for the "#REF!"? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "David" wrote: I am trying to add the total for one column in which some cells have the #REF! sign. My formula is: =SUMIF(Data!A1:Data!A100,"<#VALUE!") but the output is still #REF! and cannot display the value total. Does my formula miss any? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sum the column with the error cells
Hi,
Try this =SUMIF(B1:B11,"<9E9") This handles all error types. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "David" wrote: Thanks, Luke. I thought the <#VALUE! means check out for all criterias except the NOT VALUE data. How about to include the #NUM!? "Luke M" wrote: Your sheet reference is invalid (you truly do have a reference error). =SUMIF(Data!A1:A100,"<#VALUE!") Of note, why are you checking for "#VALUE!"? should it be searching for the "#REF!"? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "David" wrote: I am trying to add the total for one column in which some cells have the #REF! sign. My formula is: =SUMIF(Data!A1:Data!A100,"<#VALUE!") but the output is still #REF! and cannot display the value total. Does my formula miss any? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sum the column with the error cells
or
=SUMPRODUCT(--ISERROR(A1:A100)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
Set RN = Range(Cells(2, 1), Cells(r, 1)) error | Charts and Charting in Excel | |||
Copying a column of single cells into a column of merged cells | Excel Discussion (Misc queries) | |||
Column Chart Error Bar | Charts and Charting in Excel |