Count Only One Instance of a Record per category

jedi160

Board Regular
Joined
Jan 8, 2005
Messages
57
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top