Search code examples
excelexcel-formulapivot-tableworksheet-functioncountif

How to SUM the number of duplicates in a range by given value?

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.

enter image description here

Solution

  • 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.)

    Example