Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to copy sum of selected cells only in Excel?

For example you have a range of number cells in Excel, and now you need to copy this range’s sum and paste into another cell, how could you handle it quickly? This article will share some easy workarounds to copy the sum of selected cells only in Excel.

Copy sum of selected cells only with VBA

Copy sum of selected cells only with creating a name range

Copy sum of selected cells only with ignoring hidden cells/rows/columns

Easily copy subtotals only from selection to another range or worksheet in Excel

With Kutools for Excel’s Advanced Combine Rows feature, Excel users can quickly calculate the subtotals (sum, count, average, etc.) without moving same items together. Therefore, you can copy your selection to the destination range, and then apply this feature for copying subtotals only in Excel. Full Feature Free Trial 60-day!

ad advanced combine rows copy subtotals


We can apply a VBA to copy the sum of selected cells only and then paste into another cell in Excel. Please do as follows:

1. Select the number cells whose sum you will copy only, and press the Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste below VBA code into the opening Module window.

VBA: Copy sum of selected cells only in Excel

Sub CopySum()
Dim xOb As New DataObject
xOb.Clear
xOb.SetText Application.WorksheetFunction.Sum(Application.ActiveWindow.RangeSelection)
xOb.PutInClipboard
MsgBox "The sum of selected cells has been copied to the Clipboard.", vbInformation, "Kutools for Excel"
End Sub
3. Click Tools > References to open the References – VBAProject window.

4. In the References - VBAProject dialog box, please click the Browse button. Next select the FM20.DLL file in the folder of C:\Windows\System32 in the popping up Add Reference dialog box, and then click the Open button and OK button successively to close two dialog boxes. See screenshot:

Note: If the Microsoft Forms 2.0 Object Library option exists in the Available References box of References – VBAProject window, please check this option directly, and then click the OK button.

5. Now you return to the Module window, please press the F5 key or the Run button to run this VBA code.

And now a dialog box comes out and tell you the sum of selected cells has been copied to the Clipboard already. Please click the OK button to close it.

6. Go ahead to select the destination cell you will paste the sum into, and press the Ctrl + V keys simultaneously to paste the sum of selected cells.


Some Excel users may be reluctant to use the VBA code. Do not worry! This method will help you to copy the sum of selected cells only with creating a name range and applying the SUM function.

1. Select the range of cells whose sum you will copy, type a name in the Name box at the upper-left corner, and press the Enter key.

In our case, we define the name range as copy_sum in the Name box. See screenshot:

2. Select the destination cell you will paste the sum of these cells, type the formula =SUM(copy_sum) into it, and press the Enter key.

And now the sum of all cells in the name range returns in the selected cell.

Kutools for Excel’s To Actual: One click to remove formulas but keep calculated values from cells


Sometimes, there may be hidden cells, rows, or columns in the selected range whose sum you will copy. And you need to copy the sum ignoring any hidden cells, rows, and columns. How? Try this method!

1. Select the range with hidden rows and columns whose sum you will copy, type a name in the Name box at the upper-left corner, and press the Enter key. See screenshot:
In our case, we define the name range as copy_sum_visible in the Name box. See screenshot:

2. Select the destination cell you will place the sum at, and type the formula =SUMVISIBLE(copy_sum_visible) into it, and press the Enter key.

Notes:

(1) The SUMVISIBLE function is one of multiple useful built-in functions provided by Kutools for Excel.

(2) The sum value will become error if you send this workbook to others who don't install this Kutools for Excel. For the sake of avoiding this problem, we recommend you to replace the formula with its calculated value by Kutools for Excel's To Actual utility. Full Feature Free Trial 60-day!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.