CODEWITHSUNDEEP
excelexcel-formulasumpivot-tableworksheet-function
pradeep
pradeep

Reputation: 155

How to sum up the total based on occurrences in one column

CUSTOMER_SITE    INVOICE_AMOUNT
95093              14711.65
95093              282240
11111              10
11111              10
11111              10

CUSTOMER_SITE and INVOICE_AMOUNT are two columns.

I want to sum the second column based on the occurrences in the first column. Example:

11111 occurs three times so I want to know this total from column B:

11111 10 + 10 + 10 = 30

Upvotes: 0

Views: 897

Answers (2)

pnuts
pnuts

Reputation: 59495

I'd use a PivotTable, purpose designed and very easy:

SO18250133 example

Upvotes: 2

user2140173
user2140173

Reputation:

In Range("C2") stick this formula =SUMIF(A:A,A2,B:B)

Click in the right-bottom corner and drag the formula down to the last row presented in column A or B

drag formula

Select Columns A & C » right click » copy

Select Column D » Paste Special ( As Values )

paste as VALUES

Select Columns D & E and go to Data tab on the top of the toolbar and find Remove Duplicate

Remove Duplicate

Unselect Column E and click OK

Unselect Column E

Now your Columns D & E contain a distinct CUSTOMER_SITE and the total from the INVOICE_AMOUNT

finished

Upvotes: 2

Related Questions