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.

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

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