Get a total count of values in a list, excluding duplicates.

[RockandGrohl]

Hello all, bit of a problem here. Column P contains a list of 190,000 references and I would like to count how many times each name appears in the list, so I can sum up the final tally.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]P[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Adam[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dave[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Craig[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mike[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Pete[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Adam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Pete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Pete[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Adam[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The trouble is if I use a simple countif, when summing it would give Adam 9 instead of the correct 3.


My issue is that down my list of 190,000 rows, people pop in and out where they've made brochure requests over the years. So you can have the same unique reference appearing again after many thousand rows.

Now I know what you're thinking, I could just count the list and then tally that off into years and figure out how many brochure requests people make, but randomly at some point in time a year ago the database changed the way it recorded brochure requests, and long story short, it will require a formula.


What I'm trying to achieve is a unique count of every time a unique reference appears in P, so I can sum up the total amount of unique's per year and then work out the average per reference per year.

 

[steve the fish]

Use a pivot table with name in the row field and count of name in the totals field is my suggestion. It will be rapid whilst formulas will not.

 

[RockandGrohl]

Use a pivot table with name in the row field and count of name in the totals field is my suggestion. It will be rapid whilst formulas will not.

Hey, thanks for this. It works, but say I want to have a summary sheet separate from my data with the information summarised by year how could I do this?

 

[steve the fish]

I dont know. How is the data organised? Is there a year column? If so just put the year in the pivot also.

 

[RockandGrohl]

I dont know. How is the data organised? Is there a year column? If so just put the year in the pivot also.

The data is appended every month which will have another multi-thousand rows to add. The formulas are to be applied downward then copy-pasted as values. For this reason I'd rather not do a Pivot as it would require constant readjustment. Is there a formulaic way to achieve this? I was experimenting with IF(COUNTIF) where the countif range had one fixed reference and one expanding reference to say "if this identifier appears above the list don't count it" but it didn't work

 

[steve the fish]

A pivot would be simple for this. Create a table for your data. This will auto expand as you add more data. Point the pivot to the table and refresh it as you add data. Couldnt be easier.

 

[RockandGrohl]

A pivot would be simple for this. Create a table for your data. This will auto expand as you add more data. Point the pivot to the table and refresh it as you add data. Couldnt be easier.

Done that, it's pretty helpful. I pointed it towards the date column which is set like 01/01/2017, 02/01/2017 - any way to change it so it just shows me 2017 and 2018 dates instead of one field for every day of the year?

 

[steve the fish]

You should be able to do that within the pivot. If you dont see that option the easiest way is to create a new column in the data called year and calculate it from the dates =YEAR(A1).

 

[RockandGrohl]

You should be able to do that within the pivot. If you dont see that option the easiest way is to create a new column in the data called year and calculate it from the dates =YEAR(A1).

Yep, found it thanks. It's been a very long time since I used Pivot Tables!