Sumif formual help

[MrPink1986]

Hi

I have a list of data in range AE1:AE157 these are all currencies AED etc. I have a table with 4 groups in cells AN-AR G1,G2,G3,G4,G5 here lies differenct combos of currencies. In AT-AX I have a table of levels I want to apply to the range in AE in cells AF-AI.

What formula would work best to apply the level in the table group below in to table with data in yellow?
I have been advised that Sumifs would be best to use however I have never used this formula before

Any help greatly appreciated
S

[TABLE="width: 816"]
<COLGROUP><COL style="WIDTH: 48pt" span=17 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Currency[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Vendor Tolerance Spot[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Vendor Tolerance Forward[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Day on Day Spot Tolerance[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Day on Day Forward Tolerance[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G1[/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G2[/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G3[/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G4[/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G5[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]Reportable group[/TD]
[TD="class: xl66, width: 128, bgcolor: transparent, colspan: 2"]Vendor to Vendor tolerance (bps)[/TD]
[TD="class: xl66, width: 128, bgcolor: transparent, colspan: 2"]Day/Day tolerance (bps)[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]AUD[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]EUR[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]ZAR[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]BRL[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]KES[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]AMD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Spot[/TD]
[TD="class: xl70, bgcolor: transparent"]Forward[/TD]
[TD="class: xl70, bgcolor: transparent"]Spot[/TD]
[TD="class: xl70, bgcolor: transparent"]Forward[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]CAD[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]GBP[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]MXN[/TD]
[TD="class: xl71, bgcolor: #c4bd97"]ARS[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]GHS[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]ERN[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G1[/TD]
[TD="class: xl72, bgcolor: transparent"]25[/TD]
[TD="class: xl72, bgcolor: transparent"]50[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]200[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]CHF[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]JPY[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]KRW[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]RON[/TD]
[TD="class: xl71, bgcolor: #c4bd97"]KZT[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]GMD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G2[/TD]
[TD="class: xl72, bgcolor: transparent"]50[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]200[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]GBP[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]CHF[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]INR[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]HRK[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]PKR[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]MWK[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G3[/TD]
[TD="class: xl72, bgcolor: transparent"]50[/TD]
[TD="class: xl72, bgcolor: transparent"]200[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]200[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HKD[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]AUD[/TD]
[TD="class: xl71, bgcolor: #c4bd97"]RUB[/TD]
[TD="class: xl71, bgcolor: #c4bd97"]EGP[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]VND[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]MZN[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G4[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]300[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]200[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]JPY[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]CAD[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]TRY[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]MAD[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]ALL[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]ZWR[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: #a6a6a6"]G5[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]USD[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="class: xl67, bgcolor: yellow"]data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]HKD[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]IDR[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]TND[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]BAM[/TD]
[TD="class: xl68, width: 64, bgcolor: #a6a6a6"]SLL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Andrew Poulsom]

Formula in AF2 copied down and across:

Excel 2010
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1	Currency	Vendor Tolerance Spot	Vendor Tolerance Forward	Day on Day Spot Tolerance	Day on Day Forward Tolerance		G1	G2	G3	G4	G5		Reportable group	Vendor to Vendor tolerance (bps)		Day/Day tolerance (bps)
2	AUD	25	50	100	200		EUR	ZAR	BRL	KES	AMD			Spot	Forward	Spot	Forward
3	CAD	25	50	100	200		GBP	MXN	ARS	GHS	ERN		G1	25	50	100	200
4	CHF	25	50	100	200		JPY	KRW	RON	KZT	GMD		G2	50	100	100	200
5	GBP	25	50	100	200		CHF	INR	HRK	PKR	MWK		G3	50	200	100	200
6	HKD	25	50	100	200		AUD	RUB	EGP	VND	MZN		G4	100	300	100	200
7	JPY	25	50	100	200		CAD	TRY	MAD	ALL	ZWR		G5	N/A	N/A	N/A	N/A
8	USD	#VALUE!	#VALUE!	#VALUE!	#VALUE!		HKD	IDR	TND	BAM	SLL
Sheet1

Cell Formulas
Range	Formula
AF2	=INDEX(AR$3:AR$7,MATCH(INDEX($AK$1:$AO$1,MAX(INDEX(($AK$2:$AO$8=$AE2)*(COLUMN($AK$1:$AO$1)-COLUMN($AK$1)+1),))),$AQ$3:$AQ$7,FALSE))

 

[socool111]

So there is only 1 Currency per group (IE: EUR cant be in G1 AND G2)?...Also, do you simply wish to input the cell reference into the Yellow spots...so in the first column for CAD would you just want 25, 50, 100, 200? or is there more to it

 

[MrPink1986]

Hi Socoll111

Yes each group will only have reference to 1 currency there will be no corss over.

Well the yellow spots will be populated by the reportable group table - G1,G2,G3 etc have different levels (in the grey table in the middle) There are then 4 different secnarios to populate in the yellow spaces -all covered in the reportable group table

 

[Andrew Poulsom]

Did you try the formula I suggested in Post #2?

 

[MrPink1986]

Hi Andrew,

I have just logged on to the spreadsheet now - I am retunring an n/a value in the cells AF - AI

 

[Andrew Poulsom]

You may need to adjust the range references to suit your setup. You can see that the formula is working from my sample.

 

[MrPink1986]

You may need to adjust the range references to suit your setup. You can see that the formula is working from my sample.

Many thanks that has worked a treat - if you wouldnt mind could you break down that formula for me please?

 

[Andrew Poulsom]

The Evaluate Formula tool will show you how the formula works.