Count distinct entries only

Catalin.B · #3 10-12-2014, 08:42 PM

Hi gebobs,
The formula used in the attached file is:
=SUM(IF(FREQUENCY(IF($A$2:$A$27&$C$2:$C$27=A20&C20 ,MATCH($B$2:$B$27,$B$2:$B$27,0)),IF($A$2:$A$27&$C$ 2:$C$27=A20&C20,MATCH($B$2:$B$27,$B$2:$B$27,0)))>0 ,1))
It’s an array formula, needs to be confirmed with CSE (Ctrl+Shift+Enter, not just Enter after editing the formula)
The key is the IF condition that sends the Type Column to Frequency function, but only the rows that contains Flange&Chocolate, Flange&Vanilla,Widget&Chocolate in joined columns A and C.
There is a typo error in Log sheet, Type column in cell B21, Yellow value has a trailing space, that is wrongly identifying this row as unique. I recommend using data validation lists, to avoid errors like this.
Cheers,
Catalin

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula to count entries as per month, year and submission wise from different sheets	pinkacidpunk	Excel	1	05-28-2013 08:26 AM
Clipboard - old entries	whipwell	Office	1	11-14-2012 02:18 AM
formula taking long time to distinct word	gsrikanth	Excel	2	03-18-2012 03:30 AM
colour of entries	ntambomvu	Outlook	0	03-15-2012 11:56 PM
Autocorrect entries with astrix at the end	dswapp	Word	2	02-14-2011 09:34 AM