"Count duplicates" can mean four different things in Google Sheets: how many times each value appears, how many rows are repeats, how many different values there are, or a full frequency table. This page gives a formula for each, then the versions for several columns.
Header in row 1, items in A2:A7: Apple, Banana, Apple, Cherry, Banana, Date. COUNTIF ignores case, so "apple" would be counted as "Apple".
In B2, fill down:
=IF(A2="","",COUNTIF($A$2:$A$100,A2))
B2:B7 gives 2, 2, 2, 1, 2, 1. Each row shows how many times its value appears in the whole list, including itself.
For a running count, where the first copy is 1 and the next is 2:
=IF(A2="","",COUNTIF($A$2:A2,A2))
B2:B7 gives 1, 1, 2, 1, 2, 1. Anything above 1 is a repeat, so you can filter on it.
To count one specific value, use =COUNTIF(A2:A7,"Banana"), which gives 2.
This depends on whether you count the first copy.
Extra copies, meaning the rows you would delete to keep one of each:
=COUNTA(A2:A7)-COUNTUNIQUE(A2:A7)
Result: 2 (the second Apple and the second Banana).
Every row that has a twin, first copies included:
=SUMPRODUCT(--(COUNTIF(A2:A7,A2:A7)>1))
Result: 4 (both Apples and both Bananas). Use the exact data range here, not A2:A100, so empty cells stay out of the count.
=COUNTUNIQUE(A2:A7)
Result: 4 (Apple, Banana, Cherry, Date). Each different value counts once.
Values that appear exactly once:
=SUMPRODUCT(--(COUNTIF(A2:A7,A2:A7)=1))
Result: 2 (Cherry and Date). The number of different values that repeat is then the first result minus the second:
=COUNTUNIQUE(A2:A7)-SUMPRODUCT(--(COUNTIF(A2:A7,A2:A7)=1))
Result: 2 (Apple and Banana).
Put "Fruit" in A1 so the data is A1:A7. In D1:
=QUERY(A1:A7,"select A, count(A) where A is not null group by A label count(A) 'Count'",1)
Result: a header row Fruit, Count, then Apple 2, Banana 2, Cherry 1, Date 1.
To list the most frequent first, add an order clause (it goes before label):
=QUERY(A1:A7,"select A, count(A) where A is not null group by A order by count(A) desc, A label count(A) 'Count'",1)
To show only the values that repeat, QUERY has no HAVING clause, so wrap one QUERY in another:
=QUERY(QUERY(A1:A7,"select A, count(A) where A is not null group by A label count(A) 'Count'",1),"select * where Col2 > 1",1)
Result: Fruit, Count, Apple 2, Banana 2.
In D2:
=UNIQUE(A2:A7)
In E2:
=ARRAYFORMULA(IF(D2:D="","",COUNTIF($A$2:$A$7,D2:D)))
D2:D5 shows Apple, Banana, Cherry, Date and E2:E5 shows 2, 2, 1, 1. Leave the cells below D2 empty, because UNIQUE fills them.
Sample in A1:B5 (Name, City): Priya Sydney; Tom Perth; Priya Sydney; Priya Melbourne.
Count of each combination, in C2 filled down:
=IF(A2="","",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2))
C2:C5 gives 2, 1, 2, 1. Priya in Melbourne is a different row from Priya in Sydney.
Rows that have a twin:
=SUMPRODUCT(--(COUNTIFS(A2:A5,A2:A5,B2:B5,B2:B5)>1))
Result: 2.
Different combinations:
=SUMPRODUCT(1/COUNTIFS(A2:A5,A2:A5,B2:B5,B2:B5))
Result: 3, so there is 1 extra copy (4 rows minus 3). Use exact ranges, because a blank row would break the division.
A frequency table of combinations:
=QUERY(A1:B5,"select A, B, count(A) where A is not null group by A, B label count(A) 'Count'",1)
Result: Priya Melbourne 1, Priya Sydney 2, Tom Perth 1.
Counting one value anywhere in a block works with a two-column range: =COUNTIF(A2:B5,"Priya") gives 3. Be careful with COUNTUNIQUE on two columns. It counts different cell values across both columns (here 5: Priya, Sydney, Tom, Perth, Melbourne), not different rows. Use the COUNTIFS version above for rows.
Extra spaces. "Date " and "Date" are different. Use Data > Data cleanup > Trim whitespace first.
Numbers stored as text. "1001" typed as text does not match the number 1001.
Case. COUNTIF treats Apple and apple as the same. If you need them separate, use SUMPRODUCT(--EXACT(range,A2)).
A range that stops too early. If your data goes past row 100, widen it.
Spelling differences. "Jon Smith" and "John Smith" are different values, so these formulas count them separately.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Counting needs exact matches, and the formulas above are all most people need. If your list has the same name spelled several ways, Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas can group near-matches first, but it will not change anything for clean data.
Duplicate Remover: Remove Duplicates in Sheets, our free add-on, is coming soon to the Google Workspace Marketplace.
Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.