# count without dublication

#### xaritona89

##### New Member
Base:

 karada M mexamridi abonent whith formul"s 1200-027 1200-43 1200-113 29 1 1200-027 1200-42 1200-114 35 1 1200-027 1200-32 1200-126 53 1 1200-027 1200-26 1200-127 53 1 1200-028 1200-27 1200-127 35 1 1200-028 1200-28 1200-114 35 1

<tbody>
</tbody>

Question:
count "mexamridi" (with out dublication)

 karada count-"mexamridi" sum-"abonent" count "mexamridi" (with out dublication) 1200-027 4 170 ? 1200-028 2 70 ?

<tbody>
</tbody>

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

##### MrExcel MVP
What is the expected count for mexamridi without duplication?

#### xaritona89

##### New Member
Excuse me (

 karada M mexamridi abonent whith formul"s 1200-027 1200-44 1200-113 29 1 1200-027 1200-44 1200-114 35 1 1200-027 1200-32 1200-126 53 1 1200-027 1200-32 1200-127 53 1 1200-028 1200-27 1200-127 35 1 1200-028 1200-28 1200-114 35 1

<tbody>
</tbody>

Question:
1200-027 and 1200-028 count "M" (with out dublication)

 karada count-"mexamridi" sum-"abonent" count "M" (with out dublication) 1200-027 4 170 ? 1200-028 2 70 ?

<tbody>
</tbody>

? = 2
? = 2

##### MrExcel MVP
 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ I​ 1​ karada M mexamridi abonent 2​ 2​ 1200-027 1200-44 1200-113 29​ karada mexamridi count abonent sum M distinct 3​ 1200-027 1200-44 1200-114 35​ 1200-027 4​ 170​ 2​ 4​ 1200-027 1200-32 1200-126 53​ 1200-028 2​ 70​ 2​ 5​ 1200-027 1200-32 1200-127 53​ 6​ 1200-028 1200-27 1200-127 35​ 7​ 1200-028 1200-28 1200-114 35​ 8​

In F1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$7=""),MATCH(\$A\$2:\$A\$7,\$A\$2:\$A\$7,0)),ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1),1))

In F3 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$F\$3:F3)>\$F\$1,"",INDEX(\$A\$2:\$A\$7,SMALL(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$7=""),MATCH(\$A\$2:\$A\$7,\$A\$2:\$A\$7,0)),ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1),ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1),ROWS(\$F\$3:F3))))

In G3 just enter and copy down:

=IF(\$F3="","",COUNTIFS(\$A\$2:\$A\$7,\$F3,\$C\$2:\$C\$7,"?*"))

In H3 just enter and copy down:

=IF(\$F3="","",SUMIFS(\$D\$2:\$D\$7,\$A\$2:\$A\$7,\$F3))

In I3 control+shift+enter, not just enter, and copy down:

=IF(\$F3="","",SUM(IF(FREQUENCY(IF(1-(\$B\$2:\$B\$7=""),IF(\$A\$2:\$A\$7=\$F3,MATCH(\$B\$2:\$B\$7,\$B\$2:\$B\$7,0))),ROW(\$B\$2:\$B\$7)-ROW(\$B\$2)+1),1)))

Replies
2
Views
305
Replies
16
Views
305
Replies
4
Views
97
Replies
29
Views
1K
Replies
9
Views
143

1,133,253
Messages
5,657,619
Members
418,404
Latest member
Sneijders

### 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.

### Which adblocker are you using?

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

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