Excel Formula - Count duplicates in Column B per Name (Column A)?

[DaleVacaro]

Hi All,

Sorry not good with excel formula, can you help me construct formula how can I count how many duplicates we have in Column B per Name (Column A)?

Sample result should be:

Rose - 1 (2 Red)

Joy - 1 (2 Purple)

Angel - 2 (2 Blue & 2 Red)

l

 

[etaf]

using a countifs() and copy down
=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)

Then you will see against the cell how many found

or a pivot table may help here

 

[etaf]

Example Excel Forum 2.xlsx
	A	B	C	D	E	F	G	H
1	name	Colour	Duplicates			name	Colour	Count of Duplicates
2	rose	blue	1			angel	blue	2
3	rose	blue	1			rose	blue	2
4	rose	red	1				red	2
5	rose	red	1			Grand Total		6
6	angel	blue	1
7	angel	blue	1
8	angel	pink
9
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2)>1,COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2)/2,"")

 

[Peter_SSs]

Duplicate to: Count of Duplicates (Column B) per Name (Column A)

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.