Count - Special case

**shiyaschennattu** · 01-02-2013, 07:56 AM

Hello,

In my excel sheet i have two columns like A (Model) and B(Sl.No),few models are repeated with same serial number. so i need to count it without duplication.so i use this formula.
=COUNT(1/FREQUENCY(IF(A2:A100&B2:B100<>"",MATCH(A2:A100&B2:B100,A2:A100&B2:B100,0)),ROW(A2:A100)-ROW(A2)+1))

Then i got the total count of model(Ex:2523 C) - serial number(Ex:MEC45KHJUH41) without duplication(15),but i need to know the count of each models in the result of 15. Please find the attachment and see the commends inserted for more details.

Please help me, i tried and left 2 days for this.

**bebo021999** · 01-02-2013, 10:56 AM

Try this:

=SUM(IF(FREQUENCY(IF($A$2:$A$19=G8,MATCH($B$2:$B$19,$B$2:$B$19,0)),ROW($A$2:$A$19)-ROWS($A$2)+1),1))

Confirmed with Ctrl-shift-enter

**shiyaschennattu** · 01-02-2013, 12:49 PM

Thank you Sir..that was very valuable for me. Thank you very much.

**shiyaschennattu** · 01-03-2013, 03:22 AM

Sir,

I got the correct answer in my excel sheet (Question.xls), But i apply the same equation to my original excel sheet(same format as Question.xls) but there i don't get the correct answer. I use correct formula as you told. please find my original excel sheet and read the commend inserted and yellow marking fields. kindly help me sir.

**bebo021999** · 01-03-2013, 12:46 PM

That is my failure in building the formula, if the starting cell from row 20, it is false. So correct:
=SUM(IF(FREQUENCY(IF($I$20:$i$500=H11,MATCH($K$20:$K$500,$K$20:$K$500,0)),ROW($I$20:$J$500)-ROW($I$20)+1),1))
Pls try to put $ sign before range if needed to avoid inexpected range moving after copying