+ Reply to Thread
Results 1 to 6 of 6

Sorting in a Column & Totaling Question

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    Sorting in a Column & Totaling Question

    I'm hoping someone can assist me with this. I have a column (C) which contains multiple account #'s 30,000 plus. There could be multiple duplicate account #'s but the same account #'s will be together (i.e. c6,c7,c8, etc) until a different account # appears in column C (i.e. c9,c10,11, etc). In column (H) there is a usage # which will be affiliated to each account number. I need to figure a formula which will identifiy all of the matching account #'s in column "C", then for that particular account # add all of the affiliated numbers in column "H" for a total. This needs to be repeated for all of the individual account #'s in column "C". Any suggestions would be greatly appreciated!

    In my example I color coded two separate accounts as an example for both column c & h. I need to total the numbers in "H" for each account. I hope this makes sense! Thanks in advance!!
    Attached Files Attached Files
    Last edited by lilsnoop; 12-18-2007 at 06:05 PM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Here's one way, try this in J6

    =C6, then in J7

    =IF(C7=C6,"",C6) copy down column J, then in K6

    =SUMPRODUCT(--($C$6:$C$72=J6)*($H$6:$H$72)) copy down column K

    This will total against each change of account number, where no account number zero, then copy > paste special values and sort column J & K by J
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    Thanks OldChippy

    Thank you for your help. I tried what you suggested and I seem to be getting a #value! error sign in column "K". Would it be possible for you to show me what you did using my sample attachment? Thanks again!!

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Here you go, all you need to do then is to copy columns J & K, then Paste Special > Values, then sort by column J
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    Thanks Oldchippy

    Your assistance was greatly appreciated!!!

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Glad to help - thanks for the feedback

+ 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