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

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)

AUD

data

data

data

data

EUR

ZAR

BRL

KES

AMD

Spot

Forward

Spot

Forward

CAD

data

data

data

data

GBP

MXN

ARS

GHS

ERN

G1

25

50

100

200

CHF

data

data

data

data

JPY

KRW

RON

KZT

GMD

G2

50

100

100

200

GBP

data

data

data

data

CHF

INR

HRK

PKR

MWK

G3

50

200

100

200

HKD

data

data

data

data

AUD

RUB

EGP

VND

MZN

G4

100

300

100

200

JPY

data

data

data

data

CAD

TRY

MAD

ALL

ZWR

G5

N/A

N/A

N/A

N/A

USD

data

data

data

data

HKD

IDR

TND

BAM

SLL

<COLGROUP><COL style="WIDTH: 48pt" span=17 width=64><TBODY>
</TBODY>

 

[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.