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

H

h20polo

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top