count without dublication

[xaritona89]

Please help me
Base:

[TABLE="width: 532"]
<tbody>[TR]
[TD]karada[/TD]
[TD]M[/TD]
[TD]mexamridi[/TD]
[TD]abonent[/TD]
[TD]whith formul"s[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-43[/TD]
[TD]1200-113[/TD]
[TD]29[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-42[/TD]
[TD]1200-114[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-32[/TD]
[TD]1200-126[/TD]
[TD]53[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-26[/TD]
[TD]1200-127[/TD]
[TD]53[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]1200-27[/TD]
[TD]1200-127[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]1200-28[/TD]
[TD]1200-114[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Question:
count "mexamridi" (with out dublication)

[TABLE="width: 641"]
<tbody>[TR]
[TD]karada[/TD]
[TD]count-"mexamridi"[/TD]
[TD]sum-"abonent"[/TD]
[TD]count "mexamridi" (with out dublication)[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]4[/TD]
[TD]170[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]2[/TD]
[TD]70[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What is the expected count for mexamridi without duplication?

 

[xaritona89]

Excuse me (
Please help me

[TABLE="class: cms_table, width: 532"]
<tbody>[TR]
[TD]karada[/TD]
[TD]M[/TD]
[TD]mexamridi[/TD]
[TD]abonent[/TD]
[TD]whith formul"s[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-44[/TD]
[TD]1200-113[/TD]
[TD]29[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-44[/TD]
[TD]1200-114[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-32[/TD]
[TD]1200-126[/TD]
[TD]53[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]1200-32[/TD]
[TD]1200-127[/TD]
[TD]53[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]1200-27[/TD]
[TD]1200-127[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]1200-28[/TD]
[TD]1200-114[/TD]
[TD]35[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: cms_table, width: 641"]
<tbody>[TR]
[TD]karada[/TD]
[TD]count-"mexamridi"[/TD]
[TD]sum-"abonent"[/TD]
[TD]count "M" (with out dublication)[/TD]
[/TR]
[TR]
[TD]1200-027[/TD]
[TD]4[/TD]
[TD]170[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]1200-028[/TD]
[TD]2[/TD]
[TD]70[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


answer:
? = 2
? = 2

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]karada[/td][td]M[/td][td]mexamridi[/td][td]abonent[/td][td][/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]1200-027[/td][td]1200-44[/td][td]1200-113[/td][td]

29​

[/td][td][/td][td]karada[/td][td]mexamridi count[/td][td]abonent sum[/td][td]M distinct[/td][/tr]
[tr][td]

3​

[/td][td]1200-027[/td][td]1200-44[/td][td]1200-114[/td][td]

35​

[/td][td][/td][td]1200-027[/td][td]

4​

[/td][td]

170​

[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]1200-027[/td][td]1200-32[/td][td]1200-126[/td][td]

53​

[/td][td][/td][td]1200-028[/td][td]

2​

[/td][td]

70​

[/td][td]

2​

[/td][/tr]
[tr][td]

5​

[/td][td]1200-027[/td][td]1200-32[/td][td]1200-127[/td][td]

53​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]1200-028[/td][td]1200-27[/td][td]1200-127[/td][td]

35​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]1200-028[/td][td]1200-28[/td][td]1200-114[/td][td]

35​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)))

 

[xaritona89]

Aladin Akyurek

thank you friend )

 

[Aladin Akyurek]

Aladin Akyurek

thank you friend )

You are welcome.