How to SUM the number of duplicates in a range by

问题:

A simple example as below:

Column A is the list containing all data
Column B is the list of all unique data in Column A without duplicates
Column C will be the number of duplicates for each item in Column B from Column A

How to write a easy formula for cells in Column C to sum each item in Column B from Column A?

I used =sum(lookup($a$1:$a$10=$B$1)) for C1 but received: too few arguments for this function.

回答1:

A simple countif() should suffice unless I'm misunderstanding..

=COUNTIF($A$1:$A$10,B1)

Count if returns the number of times the second argument is found in the first argument (which is a range.)

回答2:

With a PivotTable may be even simpler:

The unique list is created as part of constructing the PivotTable, and is sorted alphabetically. A Total may also be automatic, for control purposes.