SUMPRODUCT - SUMIF - MULTIPLE TABLES; To Get Results From Single Criteria

[MixedUpExcel]

Hi,

I can get so far with my formula but not sure how to finish it off. Please help.

I have 4 tables, Table 2 / 3 and 4 have data - the first table has the initial criteria and the formula next to it to bring back the result.

	B	C	D	E	F	G	H	i	J	K	L
2	TABLE 1				TABLE 2
3		JAN
4	N. England - Sales				N. England - Sales	Northern England
5	N. England - Margin				N. England - Margin	Northern England
6	Central England - Sales				Central England - Sales	Central England
7	Central England - Margin				Central England - Margin	Central England
8	Scotland & N. Ireland - Sales				Scotland & N. Ireland - Sales	Scotland & Northern Ireland
9	Scotland & N. Ireland - Margin				Scotland & N. Ireland - Margin	Scotland & Northern Ireland
10	S. East England - Sales				S. East England - Sales	South East England
11	S. East England - Margin				S. East England - Margin	South East England
12	S. W. England & Wales - Sales				S. W. England & Wales - Sales	South West England & Wales
13	S. W. England & Wales - Margin				S. W. England & Wales - Margin	South West England & Wales
14
15	TABLE 4				TABLE 3
16	Paul	10			Northern England	Paul
17	Mark	20			Northern England	Mark
18	James	30			Northern England	James
19	Peter	40			Northern England	Peter
20	Craig	50			Northern England	Craig
21	Andrea	60			Central England	Andrea
22	Claire	70			Central England	Claire
23	Donna	80			Scotland & Northern Ireland	Donna
24	Amanda	90			South East England	Amanda
25	Rick	100			South East England	Rick
26	Julie	110			South East England	Julie
27	Kate	120			South West England & Wales	Kate
28	Lisa	130			South West England & Wales	Lisa
29
30	First Example
31	N. England - Sales			Result		150
32				150		=SUMPRODUCT(SUMIF($B$16:$B$28,IF($F$16:$F$28=B33,$G$16:$G$28),$C$16:$C$28))
33	Northern England
34
35	Paul	10
36	Mark	20
37	James	30
38	Peter	40
39	Craig	50
40

<colgroup><col><col><col span="3"><col><col><col span="5"></colgroup><tbody>
</tbody>

In Cell C4, I want to put the formula which looks for 'N. England - Sales' in Table 2. This will result in 'Northern England' as the next criteria. Using 'Northern England' in Table 3, I will get Paul / Mark / James / Peter / Craig as the next criteria. Using those 5 criteria in Table 4, it will sum up 10 / 20 / 30 / 40 / 50 and give me a result of 150 in Cell C4.

The following formula only uses 3 of the tables, I can't work out how to put the final step into it.

=SUMPRODUCT(SUMIF($B$16:$B$28,IF($F$16:$F$28=B33,$G$16:$G$28),$C$16:$C$28))

Instead of using B33 in the formula, I would like to use Table 2 to get this part of the formula.

How do I do this please? Please stick with SUMPRODUCT as the base formula as I may need to 'tweak' it a little in the future.

Thanks in advance for any help.

Simon

 

[AlanY]

try replace B33 with VLOOKUP(B4,$F$4:$G$13,2,0)

=SUMPRODUCT(SUMIF($B$16:$B$28,IF($F$16:$F$28=[COLOR="#B22222"]VLOOKUP(B4,$F$4:$G$13,2,0)[/COLOR],$G$16:$G$28),$C$16:$C$28))

 

[MixedUpExcel]

Thanks AlanY.

Not what I expected but a simple solution that I wish I'd though of

Thanks for the quick response. That appears to do the job.

Thanks again.

Simon

 

[AlanY]

you're welcome