# 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
###### Who is Participating?
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.

Commented:
Use this formula..

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

or

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

Saurabh...
0
Author Commented:
I was just hoping something easier to click to exclude and then put it back :)
I guess not..
0
EngineerCommented:
I would do

=SUM(K16:K34)-K20-K22
0
Author Commented:
Thanks guys.  I knew I could do it.  Was just hoping for something easier like click->exclude.  I guess not then.
0
Chief 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
0

Experts Exchange Solution brought to you by

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

Author 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 :)
0
Chief 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.
0
Chief 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.
0
Finance 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
0
Finance 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
0
###### 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.