Easy way to exclude cells in Excel Sum formula?

I have a column of numbers. I want to temporarily exclude few cells from being included in the Sum formula.  Then add them back.
Right now I have =SUM(K16:K34)
I want to exclude K20 and K22.  

 Is there an easy way to do this?
LVL 17
Tiras25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this formula..

 =SUM(K16:K34)-sum(K20,K22)

or

=sum(K16:K19,K21,K23:K34)

Saurabh...
Tiras25Author Commented:
I was just hoping something easier to click to exclude and then put it back :)
I guess not..
Saqib Husain, SyedEngineerCommented:
I would do

=SUM(K16:K34)-K20-K22
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Tiras25Author Commented:
Thanks guys.  I knew I could do it.  Was just hoping for something easier like click->exclude.  I guess not then.
EirmanChief Operations ManagerCommented:
Hide Column M in the attached Spreadsheet
To exclude an item from the total, change a 1 to a zero in column L
exclude.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tiras25Author Commented:
Very cool idea.  Thanks Eirman!
I was still thinking to something simpler like point-n-click but maybe this is just in my head :)
EirmanChief Operations ManagerCommented:
Your imagination is not running away from you!
It can be done with checkboxes where items in column L (now hidden) change from 1 to 0 (true/false)
http://blog.contextures.com/archives/2013/07/09/use-check-box-result-in-excel-formula/

Unfortunately, I don't have the time to do it for you.
EirmanChief Operations ManagerCommented:
Do it yourself in stages and keep asking questions here.
Post a sample workbook of your progress, and experts are more likely edit it and post it back.
Rob HensonFinance AnalystCommented:
For a small range such as you are describing; highlight the first block, press ctrl and then highlight the next block. Only those required will be highlighted and the total will show on the status bar.

Thanks
Rob H
Rob HensonFinance AnalystCommented:
Alternatively, if you do want a point & click type scenario, you could have a VBA routine that toggles the values in the "Exclude" column between 1 or 0 and then different approach using one of the following:

1) SUMIF
=SUMIF(L16:L32,1,K16:K32)

This will add up column K where column L = 1

2) SUMPRODUCT
=SUMPRODUCT(K16:K32,L16:L32)

This is effectively doing the same as Eirman's suggestion. His column M is multiplying K and L together and then summing it. SUMPRODUCT does that but in one function. It will multiply the contents of the equivalent cells from the two (or more) ranges and then sum the results.

Thanks
Rob H
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.