SOLVED

Excel SUM Function - Trying to Exclude Some Non-Contiguous Cells

Copper Contributor

I would like to Sum a column but exclude certain cells from that summing process, as they contain subtotals (so I do not want to add them twice).  I have searched the web high and low and tried multiple formulas without success.  The best I can get is a #VALUE! error.  I know I've done this type of formula in the past with an earlier version of Excel, but cannot do it with this version (Office 10 Pro).  Thanks.

6 Replies

@technonot 

It's better to use SUBTOTAL() as

=SUBTOTAL(9,C5:C9)

for intermediate results and

=SUBTOTAL(9,C3:C18)

for grand total. Please check in attached file.

Hi Sergei and thanks for your response, but when I open this doc in protected view on my computer, this is what I see (see attached) - a bunch of ###### signs in the Total column.  Why??

Also, I have never used the SUBTOTAL formula before.  What is the 9 for in the formula?  

best response confirmed by technonot (Copper Contributor)
Solution

@technonot 

Make the column wider , in protected view it doesn't expand automatically. It shall be as

image.png

9 means sum. Please check SUBTOTAL function 

@Sergei Baklan 

 

Oh, duh! on the column width, I feel dumb!  But thank you so much for explaining the 'Subtotal' function and what the 9 means; apparently I have never used this before and did not even realize it existed.  You have definitely fixed my issue!  :)

@technonot , you are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by technonot (Copper Contributor)
Solution

@technonot 

Make the column wider , in protected view it doesn't expand automatically. It shall be as

image.png

9 means sum. Please check SUBTOTAL function 

View solution in original post