countif, but filtering out "duplicates" from second column

[thebusyant]

Hi, I'm using Excel 2008 (for Mac, but I don't think that matters).

I have a table that has 2 columns like this (I'm using commas to separate the cells)

a, ethel
a, fred
b, fred
c, fred
c, fred
c, ethel
d, ethel
e, lucy
z, ricky
s, ricky
s, ricky
s, lucy

I'd like to count all occurrences of "ethel" and all occurrences of "fred" and all occurences of "lucy" etc. EXCEPT that I only want to want to count each name ONCE even if it occurs more than once when paired with the letters in the first column.

For example, in the sample shown above, there are 2 rows that say "c, fred", but I only want to count "fred" once because I consider the second instance of "c, fred" to be a duplicate.

Using the data in the example above, I would expect to see a summary table like this:

fred, 3
ethel, 3
lucy, 2
ricky, 2

Is there a relatively simple formula to do this (I really need this to work without a VBA macro)? This seems like it needs a countif function, but with some additional wrinkle thrown in.

Thanks for any hints!

[DonkeyOte]

Regards avoidance of VBA, correct, there is no VBA support in XL2008.

If we assume your table is A2:B13 then with the names detailed in D2:D5 [we assume row 1 will contain headers] we can generate the count in E2:E5 as follows:

Please Login or Register  to view this content.

It should be noted the above is not efficient and should your real data set prove to be very big you would best served changing approach.
(Note: you could also use a Frequency Array)

For a really efficient calculation IMO you should first sort your 2 column table by Columns 2 & 1 (in that order) which would in turn permit use of very efficient "helper" calculations. Whether or not that is viable is of course unknown at this stage.

If it transpires that you wish to generate the names as part of the calculation I would suggest using a helper based calculation to generate the name when appropriate (first instance of combination) in a further column which would then act as the source for a Pivot Table.