count without dublication

xaritona89

New Member
Joined
Mar 17, 2018
Messages
22
Please help me
Base:

karadaMmexamridiabonentwhith formul"s
1200-0271200-431200-113291
1200-0271200-421200-114351
1200-0271200-321200-126531
1200-0271200-261200-127531
1200-0281200-271200-127351
1200-0281200-281200-114351

<tbody>
</tbody>

Question:
count "mexamridi" (with out dublication)

karadacount-"mexamridi"sum-"abonent"count "mexamridi" (with out dublication)
1200-0274170?
1200-028270?

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

xaritona89

New Member
Joined
Mar 17, 2018
Messages
22
Excuse me :((
Please help me

karadaMmexamridiabonentwhith formul"s
1200-0271200-441200-113291
1200-0271200-441200-114351
1200-0271200-321200-126531
1200-0271200-321200-127531
1200-0281200-271200-127351
1200-0281200-281200-114351

<tbody>
</tbody>


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

karadacount-"mexamridi"sum-"abonent"count "M" (with out dublication)
1200-0274170?
1200-028270?

<tbody>
</tbody>


answer:
? = 2
? = 2
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
karadaMmexamridiabonent
2​
2​
1200-0271200-441200-113
29
karadamexamridi countabonent sumM distinct
3​
1200-0271200-441200-114
35
1200-027
4​
170​
2​
4​
1200-0271200-321200-126
53
1200-028
2​
70​
2​
5​
1200-0271200-321200-127
53
6​
1200-0281200-271200-127
35
7​
1200-0281200-281200-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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top