# Count Only One Instance of a Record per category

#### jedi160

##### Board Regular
I found a formula online that allowed me to count the first instance of a record, but I need one that allows me to count one instance per category. The first formula was based on a countif criteria that was nested inside a dcounta (see this webpage for more info: http://www.ozgrid.com/Excel/excel-count-one-occurrence.htm).

The problem with that formula is that it removes duplicates by continuing to expand the countif field. But let's say I have apples and oranges in column A, and in column B I have Josh, Jack, and James, and each person has more than one of each. I only want to count one apple for John, one apple for Jack, one apple for James, etc. The formula I was using above wouldn't count Jack or James's apple, because it already counted Josh's.

Does anyone know a way around this?

Thanks!

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Expanding on Chip Pearson's idea here: http://www.cpearson.com/excel/duplicat.htm

Try:

Code:
``````F4 =SUM(IF(FREQUENCY(IF(LEN(A2:A9)>0,MATCH(A2:A9,A2:A9,0)*MATCH(B2:B9,B2:B9,0),""),
IF(LEN(A2:A9)>0,MATCH(A2:A9,A2:A9,0)*MATCH(B2:B9,B2:B9,0),""))>0,1))``````
which is an array formula and must be confirmed with CTRL+SHIFT+ENTER
Book20
ABCDEF
1itempersonIs dup
2appleJosh
3appleJack
4appleJamesUnique Records6
5appleJoshdup
6orangeJosh
7orangeJack
8orangeJames
9orangeJackdup
Sheet1

Also...

1]

=SUM(IF(FREQUENCY(IF(A2:A9<>"",IF(B2:B9<>"",MATCH(A2:A9&B2:B9,A2:A9&B2:B9,0))),ROW(A2:A9)-ROW(A2)+1),1))

2]

=COUNTDIFF(IF(A2:A9<>"",IF(B2:B9<>"",A2:A9&B2:B9)),FALSE,FALSE)

The later requires the morefunc.xll add-in and is more efficient.
Both formulas are robust against empty cells.

Note that these formulas require to be confirmed with control+shift+enter, not just enter.

Thanks for the help!

Replies
2
Views
188
Replies
8
Views
951
Replies
9
Views
254
Replies
2
Views
183
Replies
4
Views
315

1,220,009
Messages
6,151,442
Members
451,028
Latest member
greekness1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back