Apr 04 2020 12:20 PM
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.
Apr 04 2020 12:29 PM
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.
Apr 04 2020 12:40 PM
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??
Apr 04 2020 12:41 PM
Also, I have never used the SUBTOTAL formula before. What is the 9 for in the formula?
Apr 04 2020 12:50 PM
SolutionMake the column wider , in protected view it doesn't expand automatically. It shall be as
9 means sum. Please check SUBTOTAL function
Apr 04 2020 03:34 PM
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! :)
Apr 05 2020 01:46 AM
@technonot , you are welcome, glad to help
Apr 04 2020 12:50 PM
SolutionMake the column wider , in protected view it doesn't expand automatically. It shall be as
9 means sum. Please check SUBTOTAL function