# Thread: counting and subtotaling list items (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: counting and subtotaling list items (Excel 2000)

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. ## 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
•