Autosum

ravia2buri

New Member
Joined
Dec 12, 2017
Messages
1
Hi can anyone help me in getting sum in column. Automatic sum to come whenever unit name changes.


Amount
Unit NameSum
10,228
A
10,228
A

9,318B

1,432B
10,652B
12,479D
400D
10,072
D

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Excel 2010
ABC
1AmountUnit NameSum
210,228A
310,228A20,456.00
49,318B
51,432B
610,652B21,402.00
712,479D
8400D
910,072D22,951.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Sheet4

Copy the formula down
Worksheet Formulas
CellFormula
C2=IF(B2=B3,"",SUMIF($B$2:B2,$B$2:B2,$A$2:A2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
ravia2buri,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that is based on your flat text display.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub SumUniques()
' hiker95, 12/12/2017, ME1034963
Application.ScreenUpdating = False
Dim r As Long, lr As Long, n As Long, i As Long, tot As Long
With ActiveSheet
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  For r = 2 To lr
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    If n = 1 Then
      .Range("C" & r).Value = .Range("A" & r).Value
    ElseIf n > 1 Then
      tot = 0
      For i = r To r + n - 1
        tot = tot + .Range("A" & i).Value
      Next i
      With .Range("C" & r + n - 1)
        .Value = tot
        .NumberFormat = "#,##0"
      End With
    End If
    r = r + n - 1
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the SumUniques macro.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,668
Messages
5,597,463
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top