adding totals in a column, not counting repetitions in another col
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. |
adding totals in a column, not counting repetitions in another col
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 "h20polo" wrote in message ... 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. |
All times are GMT +1. The time now is 10:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com