+ Reply to Thread
Results 1 to 3 of 3

Summing a column where matches adjacent column and colating results

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    2

    Summing a column where matches adjacent column and colating results

    I have a question which on the face of it seems very simple but have not found a solution whilst searching this and many other forums.
    I am using Excel 2010 and have a list of customer numbers with associated revenue. In this list the same customer number will appear repeatedly for each sale. In reality the list is over 2000 lines long with a possibility of 300 different customer.
    CUS # SALE $
    11111 10
    22222 22
    55222 32
    11111 20
    62626 43
    11111 11
    22222 30


    What I want is an output than sums the total per customer number, but cannot reference the customer number in any formulae as I will not know it in advance.
    The output should look like this

    CUS # SALE $

    11111 41
    22222 52
    55222 32
    62626 43


    I have tried with a formulae that says something like =SUMIF(A1:A2000,"criteria",B1:B2000) where criteria = 11111, however what I want is a similar formulae that dynamically subtotals all the sales per customer without me having to know in advance the customer number. I want the formulae to learn the criteria from the given data.

    Any help is appreciated.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Summing a column where matches adjacent column and colating results

    See my attached sheet and let me know if it is what you are trying to do. The formulas used are:

    To get a unique+sorted list (in cell D2 and filled down):
    =IFERROR(SMALL($A:$A,1+SUMPRODUCT(COUNTIF($A:$A,D$1:D1))),"")

    To get the sum per customer from that list (in cell E2 and filled down):
    =IF(D2="","",SUMIF($A:$A,D2,$B:$B))

    - Moo
    Attached Files Attached Files
    Last edited by Moo the Dog; 01-16-2013 at 07:38 PM.

  3. #3
    Registered User
    Join Date
    01-16-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Summing a column where matches adjacent column and colating results

    Great! thanks for the quick reply.
    I have tested your attachment and this looks like it will work well.

    Thanks again and all the best.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1