Search code examples
excelexcel-formulasumpivot-tableworksheet-function

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

Solution

  • 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