Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting and subtotaling list items (Excel 2000)

    I have two lists each has its own id field and amount. They should be equal, but they contain different numbers and also can have the same amount twice on one list and 3x on the other. I have successfully used the Extract unique items formula in C.Pearson's Duplicate and Unique items on List to get most of the unique differences identified. But the formula doesn't tell me if a number is on one list twice and the other list 3 times. So I tried making a pivot table where the $ was a row field and also a Count item. So list 1 dollar amounts would be counted and also list2 dollar amouts would be counted:I would get a 2 and a 3. But I didn't . I got 2 2-s. How would be the best way to get at this situation? Is a pivot table best or should I take some other approach? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting and subtotaling list items (Excel 2000)

    Not quote sure what you mean, but if you have created a column of unique items, you can put columns next to it that display counts. Say that the first unique item is in A5. Enter this formula in B5:

    =COUNTIF(Range1, A5)

    where Range1 is the first list (containing duplicates), and this formula in C1:

    =COUNTIF(Range2, A5)

    where Range2 is the second list (also containing duplicates). Fill down as far as needed.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting and subtotaling list items (Excel 2000)

    Attached is a sample of the dilemma. How can I get excel to identify the 250 and 15,255 count discrepancies?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting and subtotaling list items (Excel 2000)

    In cell G2 enter the formula =(B2=E2) and fill down?

    I have a feeling that is not what you want, but the problem seems to have shifted since the first post in this thread.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting and subtotaling list items (Excel 2000)

    My fault for not being clear. Another attachement shows what I am trying to create...something that looks like this. This gives the wrong answer because it drives from Amount1, I suppose, and cannot tell that the Amount 2 count is 3. Thanks.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: counting and subtotaling list items (Excel 2000)

    Essentially what Hans answered before:

    For count 1:
    =COUNTIF($B$2:$B$8,B2)

    And copy it down the rows

    For Count2
    =COUNTIF($D$2:$D$8,B2)

    You will got DUPs of some since the items in col B are duplicated. If you want create a unique list of items and then check the count of each in each column

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting and subtotaling list items (Excel 2000)

    Sorry, I don't understand this any more. See <post#=396103>post 396103</post#>.

Posting Permissions

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