count without dublication

[xaritona89]

Please help me
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>

 

[Aladin Akyurek]

What is the expected count for mexamridi without duplication?

 

[xaritona89]

Excuse me (
Please help 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>

answer:
? = 2
? = 2

 

[Aladin Akyurek]

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

 

[xaritona89]

Aladin Akyurek

thank you friend )

 

[Aladin Akyurek]

Aladin Akyurek

thank you friend )

You are welcome.