Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF excluding #N/A
I am trying to add different cells located in different worksheets (tabs)
within the same workbook. I know I can use =SUMIF(A1:A5,<#N/A) without error. The problem I am having is trying to add different cells not grouped together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, <#N/A). If B5 =#N/A for instance, then my SUMIF will not work. Any solutions? Thank you in advance for help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF excluding #N/A
I know it looks odd, but this appears to work:
=SUMIF(A1,"<#N/A") + SUMIF(A3,"<#N/A") + SUMIF(B5,"<#N/A") + SUMIF(C20,"<#N/A") "Scott A" wrote: I am trying to add different cells located in different worksheets (tabs) within the same workbook. I know I can use =SUMIF(A1:A5,<#N/A) without error. The problem I am having is trying to add different cells not grouped together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, <#N/A). If B5 =#N/A for instance, then my SUMIF will not work. Any solutions? Thank you in advance for help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF excluding #N/A
Thank you for the suggestion. I understand your recommnedation, but the
problem I run into is number of characters in the formula. I provided an abreviated version. In reality, I need to sum approx 20 cells over about 9 worksheets. Not all of the cells are in the same cell reference location. For the time, I am using a hidden sheet to capture the data grouped together and using a SUMIF from that. I am just trying to find a cleaner way. I appreciate any suggestions. "JLatham" wrote: I know it looks odd, but this appears to work: =SUMIF(A1,"<#N/A") + SUMIF(A3,"<#N/A") + SUMIF(B5,"<#N/A") + SUMIF(C20,"<#N/A") "Scott A" wrote: I am trying to add different cells located in different worksheets (tabs) within the same workbook. I know I can use =SUMIF(A1:A5,<#N/A) without error. The problem I am having is trying to add different cells not grouped together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, <#N/A). If B5 =#N/A for instance, then my SUMIF will not work. Any solutions? Thank you in advance for help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF excluding #N/A
If the only cells with #N/A are one of the cells you are referencing then you could do something like this =SUMIF(A2:B9,"<#N/A")-SUM(A3,A5,A6,A8,B2:B9) Note these cell addresses are just a sample. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Scott A" wrote: I am trying to add different cells located in different worksheets (tabs) within the same workbook. I know I can use =SUMIF(A1:A5,<#N/A) without error. The problem I am having is trying to add different cells not grouped together while using SUMIF. Example, =SUMIF(A1+A3+B5+C20, <#N/A). If B5 =#N/A for instance, then my SUMIF will not work. Any solutions? Thank you in advance for help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF excluding #N/A | Excel Discussion (Misc queries) | |||
Min Excluding Value | Excel Discussion (Misc queries) | |||
Excluding subtotals from SUMIF function | Excel Worksheet Functions | |||
Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
MIN excluding 0s | Excel Worksheet Functions |