Skip to main content

How to copy sum of selected cells only in Excel?

Author: Kelly Last Modified: 2024-11-12

For example, you have a range of number cells in Excel, and now you need to copy the sum of this range and paste it 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

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.
A screenshot showing the References option in Excel

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:
A screenshot showing the Browse button in the References – VBAProject window to select the FM20.DLL file

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.
A screenshot of the dialog box confirming the sum of selected cells has been copied to the clipboard

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.

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.

A screenshot of the Kutools for Excel Advanced Combine Rows feature used to copy subtotals

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Copy sum of selected cells only with creating a name range

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:
A screenshot of the Name box in Excel with a name range (copy_sum) defined for a selected cell range

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.
A screenshot showing the formula =SUM(copy_sum) entered in a destination cell to copy the sum

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


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

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:
A screenshot showing the Name box in Excel with a name range (copy_sum_visible) defined for a selected range with hidden cells

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.
A screenshot showing the formula =SUMVISIBLE(copy_sum_visible) used in Excel to copy the sum ignoring hidden cells

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.
A screenshot of the Kutools for Excel To Actual utility to replace the SUMVISIBLE formula with its calculated value


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

Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Related articles:

Copy formula without changing its cell references in Excel

Copy formulas from one workbook to another without link

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!