adding totals in a column, not counting repetitions in another col

[Guest]

Hi,

I have a list of names (column A) and titles (column C) compiled for all of
the guests who have attended our events. I am trying to categorize our guests
based on their titles, and have figured that out:

=SUM('Full list'!C:C)+COUNTIF('Full list'!C:C,"Chairman")+COUNTIF('Full
list'!C:C,"CEO")

However, that number includes every time "Chairman" or "CEO" comes up in
column C, and I only want it to add up each time it comes up for a different
name that appears in column A. Can I do this without deleting the duplicate
names?

Thanks very much.

 

[T. Valko]

Assuming that the same name will not have different titles:

Mr. Jones CEO
Mr. Jones Lobbyist

=SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20),--(C2:C20="CEO"))

Note: you can't use entire columns as range references (unless you're using
Excel 2007).

Any empty cells within the Name column will cause a #DIV/0! error.

Biff