(remove duplicate and count)
B bhasoriya Member Oct 4, 2016 #1 (remove duplicate and count) Attachments Remove Duplicate and Count.xlsx 12 KB · Views: 7
B bosco_yip Excel Ninja Oct 4, 2016 #2 Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards
Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards
B bhasoriya Member Oct 4, 2016 #3 bosco_yip said: Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards Click to expand... Thanks a lot.. Sir..
bosco_yip said: Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards Click to expand... Thanks a lot.. Sir..
U Ufoo Member Oct 4, 2016 #4 You could also type this formula in N3: =SUMPRODUCT(--(FREQUENCY(IF(B2:B285=$D3,MATCH(A2:A285&B2:B285,A2:A285&B2:B285,0)),ROW(B2:B285)-ROW(B2)+1)>0))
You could also type this formula in N3: =SUMPRODUCT(--(FREQUENCY(IF(B2:B285=$D3,MATCH(A2:A285&B2:B285,A2:A285&B2:B285,0)),ROW(B2:B285)-ROW(B2)+1)>0))
U Ufoo Member Oct 4, 2016 #5 bosco_yip said: Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards Click to expand... Thanks @bosco_yip for this robust formula
bosco_yip said: Try……… In N3, formula copy down : =SUMPRODUCT((K$2:K$285=M3)/COUNTIFS(J$2:J$285,J$2:J$285,K$2:K$285,K$2:K$285)) Regards Click to expand... Thanks @bosco_yip for this robust formula
