# Frequency of occuring values excluding duplicates

1. ## Frequency of occuring values excluding duplicates

Hi there, thanks for looking at my thread!

I have enclosed an example spread sheet with a small part of the table im using at the minute.

The task is to count the frequency of the occurances of various 'Mod revision's in column C. However, if the entry is a duplicate 'Mod Number', then it is to be excluded from the count.

I managed to find a very similar example online and it provided me with the following formula:

{=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$13<>"-",IF(\$C\$2:\$C\$13="A",MATCH(\$B\$2:\$B\$13,\$B\$2:\$B\$13))),ROW(\$B\$2:\$B\$13)-ROW(\$B\$2)+1),1))}

This formula works alright in some aspects, however it brings back some confusing results some times. For simplicity i reduced the size of the table from 1300 to 21. I notice that whenever i increase the parameters in the formula from 13 to 50, i start to get '#n/a# results for a number of the 'mod revisions'. Even increasing them from 13 to 20 causes some undesirable effects, as illustrated by the tables below it. Expanding from 13 to 20 causes the 'A' result to actually decrease, meanwhile the 'B' result increases a little bit.

Im not sure why the formula worked so well for the person i acquired it from, but it seems to go haywire for me! Any help on this matter would be greatly appreciated!

P.S. While messing around with it I noticed another strange effect: Whenever you change Cell B11 from 'abc1' to 'abc6' it manages to increase the 'A' count but decrease the 'B' count!  Register To Reply

2. ## Re: Frequency of occuring values excluding duplicates

=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$20<>"-",IF(\$C\$2:\$C\$20=A23,MATCH(\$B\$2:\$B\$20,\$B\$2:\$B\$20,0))),ROW(\$B\$2:\$B\$20)-ROW(\$B\$2)+1),1)) but b should =4 as its duplicated in b2 and b11  Register To Reply