Count if functions

[poolboy]

I have an excel spreadsheet with duplicate names in one column and their class year in another column, how do i use a formula to count them only once, and another formula to count by class year.

 

[SirJB7]

Hi, poolboy!


You can use Data tab, Data Tools group, Remove Duplicates button.

Asuming your data is in A and B columns, just select them, press the related button, et voila! No more duplicates.


Then in C1 type =COUNT(B:B) for first question.

For the second question, from D1 down type all the class years, and in E1 type =COUNTIF(B:B,D1) and copy down as needed.


If you want to preserve the original A&B columns, just copy them before to C&D, and adjust the two formulas.


Regards!

 

[poolboy]

Unfortunately, I have the following: names, class, type, all the same.

 

[SirJB7]

Hi, poolboy!

Could you please upload the file? Check 2nd green sticky post at this forums main page if necessary.

Regards!

 

[Luke M]

A PivotTable would be the fastest way to do this, as you could create the table, then alternatively throw Names or Class into the Data_Field area and do a count. Boom, done.


Via formula, you could do something like:

=IF(COUNTIF(A$2:A2,A2)=1,A2,"DUPLICATE")

To get a unique list (change columns if you want by class), and then to get the count you just do:

=COUNTIF(C:C,"<>DUPLICATE")