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

#### MixedUpExcel

##### Board Regular
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.

Code:
``=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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
try replace B33 with VLOOKUP(B4,\$F\$4:\$G\$13,2,0)

Code:
``=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))``

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

you're welcome

Replies
0
Views
149
Replies
1
Views
312
Replies
2
Views
284
Replies
6
Views
784
Replies
3
Views
617

1,203,468
Messages
6,055,599
Members
444,800
Latest member
KarenTheManager

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back