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?

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

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.

[TABLE="width: 689"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]I code[/TD]
[TD]Country[/TD]
[TD]Weight[/TD]
[TD][/TD]
[TD]Expected return[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EG[/TD]
[TD="align: right"]0.12[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]0.34+0.39[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EG[/TD]
[TD="align: right"]0.11[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]0.75[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EG[/TD]
[TD="align: right"]0.05[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]0.55[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EG[/TD]
[TD="align: right"]0.06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]RU[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]RU[/TD]
[TD="align: right"]0.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]CA[/TD]
[TD="align: right"]0.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]CA[/TD]
[TD="align: right"]0.12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]CA[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]US[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]US[/TD]
[TD="align: right"]0.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]US[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]IT[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]UK[/TD]
[TD="align: right"]0.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]UK[/TD]
[TD="align: right"]0.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]NL[/TD]
[TD="align: right"]0.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]CN[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Can you please help me out?
Thanks,
Masha

 

[Masha92]

I managed to do it myself ))

Thanks though!