Math + excel formula

[Masha92]

hi all,

I need an excel formula to count the number of countries per I code that breached a 30% threshold. I am struggling to get this formula done. I used countifs but I only get the number of Keys per I code that breaches the limit. For instance, in the example below, I code B has one country (KR) that only breached the 30% threshold. I need a formula that gives me 1!
Can you please help?

I Code	Key	COUNTRY OF LISTING	FMC Country weight	Formula?
A	18050	EG	0.012804	?
A	22631	EG	0.012804	?
A	40887	EG	0.012804	?
A	43489	EG	0.012804	?
A	49079	EG	0.012804	?
A	6273	ID	0.128809	?
B	6273	ID	0.133871	?
B	32199	ID	0.133871	?
A	32199	ID	0.128809	?
B	39656	ID	0.133871	?
A	39656	ID	0.128809	?
B	207104	ID	0.133871	?
A	207104	ID	0.128809	?
B	40194	ID	0.133871	?
A	40194	ID	0.128809	?
A	10919	ID	0.128809	?
B	10919	ID	0.133871	?
B	6281	ID	0.133871	?
A	6281	ID	0.128809	?
A	14940	ID	0.128809	?
B	14940	ID	0.133871	?
B	6308	ID	0.133871	?
A	6308	ID	0.128809	?
B	6309	ID	0.133871	?
A	6309	ID	0.128809	?
B	56411	KR	0.607649	?
A	56411	KR	0.584674	?
B	6387	KR	0.607649	?
A	6387	KR	0.584674	?
A	16909	KR	0.584674	?
B	16909	KR	0.607649	?
A	34543	KR	0.584674	?
B	34543	KR	0.607649	?
B	37791	KR	0.607649	?
A	37791	KR	0.584674	?
A	6426	KR	0.584674	?
B	6426	KR	0.607649	?
A	23486	KR	0.584674	?
B	23486	KR	0.607649	?
A	6439	KR	0.584674	?
B	6439	KR	0.607649	?
B	13906	KR	0.607649	?
A	13906	KR	0.584674	?
A	6442	KR	0.584674	?
B	6442	KR	0.607649	?
B	5856	MX	0.145745	?
A	5856	MX	0.140234	?
B	33863	MX	0.145745	?
A	33863	MX	0.140234	?
B	11062	MX	0.145745	?
A	11062	MX	0.140234	?
B	5862	MX	0.145745	?
A	5862	MX	0.140234	?
A	32866	MX	0.140234	?
B	32866	MX	0.145745	?
B	11059	MX	0.145745	?
A	11059	MX	0.140234	?
B	11145	MX	0.145745	?
A	11145	MX	0.140234	?
B	14930	MX	0.145745	?
A	14930	MX	0.140234	?
A	11095	MX	0.140234	?
B	11095	MX	0.145745	?
A	5854	MX	0.140234	?
B	5854	MX	0.145745	?
B	6462	PH	0.068501	?
A	6462	PH	0.065911	?
B	6463	PH	0.068501	?
A	6463	PH	0.065911	?
A	37240	PH	0.065911	?
B	37240	PH	0.068501	?
A	12081	PH	0.065911	?
B	12081	PH	0.068501	?
A	7307	PH	0.065911	?
B	7307	PH	0.068501	?
B	11050	PH	0.068501	?
A	11050	PH	0.065911	?
B	7646	PH	0.068501	?
A	7646	PH	0.065911	?
A	41588	PH	0.065911	?
B	41588	PH	0.068501	?
A	11044	PH	0.065911	?
B	11044	PH	0.068501	?
B	11046	PH	0.068501	?
A	11046	PH	0.065911	?
A	13501	PK	0.00488	?
A	64670	PK	0.00488	?
A	16781	PK	0.00488	?
A	13516	PK	0.00488	?
A	40943	PK	0.00488	?
B	6708	TR	0.044235	?
A	6708	TR	0.042562	?
A	6713	TR	0.042562	?
B	6713	TR	0.044235	?
A	42064	TR	0.042562	?
B	42064	TR	0.044235	?
A	6726	TR	0.042562	?
B	6726	TR	0.044235	?
A	23482	TR	0.042562	?
B	23482	TR	0.044235	?
A	6735	TR	0.042562	?
B	6735	TR	0.044235	?
A	13581	TR	0.042562	?
B	13581	TR	0.044235	?
A	32063	TR	0.042562	?
B	32063	TR	0.044235	?
B	6748	TR	0.044235	?
A	6748	TR	0.042562	?
A	13884	TR	0.042562	?
B	13884	TR	0.044235	?
A	64855	VN	0.020126	?
C	64855	VN	1	?
A	211439	VN	0.020126	?
C	211439	VN	1	?
C	200633	VN	1	?
A	200633	VN	0.020126	?
C	64514	VN	1	?
A	64514	VN	0.020126	?
A	788323	VN	0.020126	?
C	788323	1	1	?

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks,
Masha

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	Code	Key	COUNTRY OF LISTING	FMC Country weight		Code	Count > 30%
2	A	18050	EG	0.012804		A	1
3	A	22631	EG	0.012804		B	1
4	A	40887	EG	0.012804		C	2
5	A	43489	EG	0.012804
6	A	49079	EG	0.012804
7	A	6273	ID	0.128809
8	B	6273	ID	0.133871
9	B	32199	ID	0.133871
112	A	64855	VN	0.020126
113	C	64855	VN	1
114	A	211439	VN	0.020126
115	C	211439	VN	1
116	C	200633	VN	1
117	A	200633	VN	0.020126
118	C	64514	VN	1
119	A	64514	VN	0.020126
120	A	788323	VN	0.020126
121	C	788323	1	1
Sheet

 

[Masha92]

Flawless!!!

Did you build it up yourself?

Thank you sooo much! I really appreciate it!!

I will try to fraction it and interpret it to understand it!

Masha

 

[AhoyNC]

You're welcome. This link should help explain the formula (starting at about the 4:40 mark).
https://www.youtube.com/watch?v=27qh1i_kgCY

Also, this link may help.
https://www.exceltrick.com/how_to/count-unique-values-excel/

 

[Masha92]

Hi AhoyNC,

Can you please help me one more time?

So I need something similar to the previous one, I need to sum the weights per i Code with countries weight exceeding 30%. To elaborate more with expected return values, please check the example below.

I code	Country	Weight		Expected return
A	EG	0.12		A	0.34+0.39
A	EG	0.11		B	0.75
A	EG	0.05		C	0.55
A	EG	0.06
A	RU	0.03
A	RU	0.36
A	CA	0.05
A	CA	0.12
A	CA	0.1
B	US	0.2
B	US	0.05
B	US	0.5
B	IT	0.25
C	UK	0.05
C	UK	0.15
C	NL	0.55
C	CN	0.25

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

Can you please help me out?
Thanks,
Masha

 

[Masha92]

I managed to do it myself ))

Thanks though!