Complex Array Adjustment

[ryansm05]

Hi,

I have the below formula in cell a15/c15 which works great. However, I need to add an additional criteria from the second data set which also looks at column C and includes UK only.

Can anyone help?

Thanks in advance
Ryan

https://www.dropbox.com/s/wpyf5051jvv68ff/Forumla Help.xlsx?dl=0

 

[wideboydixon]

I adjusted the formulas for column A which made it easier:

Book1
	A	B	C	D	E	F	G	H	I	J
1			Apr	May	June	July	August	September
2			Q2	Q2	Q2	Q3	Q3	Q3	Total
3	100	Burger King	50						50
4	101	KFC		60	80	100			240
5	102	McDonalds			50			40	90
6	103	Subway	10	20					30
7	104	Wimpy		5	2.5				7.5
8
9
10
11
12		Clients	Q2	< Change this to Q3 for an example
13	100	Burger King	50
14	101	KFC	80	< Formulas Here in A/B/C
15	102	McDonalds	50
16	103	Subway	20
17
18				Apr	May	June	July	August	September
19				Q2	Q2	Q2	Q3	Q3	Q3	Total
20	100	Burger King	UK	50						50
21	105	KFC	UK
22	101	KFC	IRE		60	80	100			240
23	107	McDonalds	IRE
24	102	McDonalds	UK			50			40	90
25	114	McDonalds	UK
26	103	Subway	UK	10	20					30
27	104	Wimpy	IRE		5	2.5				7.5
28
29		Clients	Q2
30	100	Burger King	50
31	102	McDonalds	50
32	103	Subway	20
Sheet1

Cell Formulas
Range	Formula
B13	=IF($A13="","",VLOOKUP($A13,$A$3:$B$7,2,0))
B30	=IF($A30="","",VLOOKUP($A30,$A$20:$B$27,2,0))
A13	{=IFERROR(INDEX($A$3:$A$7,SMALL(IF(SUMIFS(OFFSET($C$3:$H$3,ROW($A$3:$A$7)-ROW($A$3),0),$C$2:$H$2,$C$12)>0,ROW($A$3:$A$7)-ROW($A$3)+1),ROWS($A$13:$A13))),"")}
A30	{=IFERROR(INDEX($A$20:$A$27,SMALL(IF($C$20:$C$27="UK",IF(SUMIFS(OFFSET($D$20:$I$20,ROW($A$20:$A$27)-ROW($A$20),0),$D$19:$I$19,$C$29)>0,ROW($A$20:$A$27)-ROW($A$20)+1)),ROWS($A$30:$A30))),"")}
C13	{=IF($A13="","",MAX(IF($C$2:$H$2=$C$12,IF($A$3:$A$7=$A13,$C$3:$H$7))))}
C30	{=IF($A30="","",MAX(IF($D$19:$I$19=$C$29,IF($A$20:$A$27=$A30,$D$20:$I$27))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD