Counting unique dynamic data

New Contributor

Hi Excellers

Can anyone help me please? I have a list of data (Staff Initials) at the end of a sheet showing which people do that task example below



I would like to Count the occurrences of each Staff Initial which I have done using =COUNTIF('Electronic Data recording-entry'!AZ$4:AZ$601,A2) - Electronic Data recording-entry being the sheet the data is entered into.


(I have transferred the data to a "Calculations" sheet and get the following (can't see all data - but you get the drift)



I want to be able to show each initial with the total count and delete duplicates. EG LP 9, ME 3, JJ2 etc just once 

I can do that using remove duplicates see below




However, I want it to be dynamic. People will be added to the bottom of the Data entry sheet. These could be existing initials, but new initials might be added as well.  I want to eventually pie graph each person with how many times they are the nominated as the person to do the task.


I also would like it to ignore zero values as sometimes the Staff initial is not known so that cell will be empty.


Hope this makes sense, and someone can help me out....



2 Replies
best response confirmed by Hans Vogelaar (MVP)

@Jacky_Keeling Consider using a pivot table. See attached. 


Add more staff ID's and press Refresh All on the Data ribbon. The Pivot Table gets instantly updated.



Thank you so much Riny - the Pivot Table gives me what I want but it still counts the blanks. I can work around that though. Thanks again - appreciate your help.