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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top