SUMIFS, VLOOKUP Formula

[blackmajik072]

Hi folks,

I'm trying to figure out a formula which will do a lookup of a region to find cities to sum sales in an associated table. I can manually do this by hard coding SUMIFS statements. However this is not scalable and will be too difficult to maintain before long. Any ideas on how I might achieve this? Many thanks.

Sales by Region
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]25[/TD]
[TD]97[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]12[/TD]
[TD]20[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]34[/TD]
[TD]13[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]20[/TD]
[TD]47[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]91[/TD]
[TD]177[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]












Region/City Lookup Table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Region[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Sydney[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Brisbane[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Hobart[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Newcastle[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Perth[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Gold Coast[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Broome[/TD]
[/TR]
</tbody>[/TABLE]















Sales by City
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]City[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Broome[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[TD]20[/TD]
[TD]47[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]Gold Coast[/TD]
[TD]34[/TD]
[TD]13[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD]11[/TD]
[TD]43[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Newcastle[/TD]
[TD]14[/TD]
[TD]54[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]City[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 64, bgcolor: transparent"]Feb[/TD]
[TD="width: 64, bgcolor: transparent"]Mar[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Broome[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Melbourne[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]47[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]51[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Gold Coast[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Sydney[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]43[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Newcastle[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]54[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]32[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Perth[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

1. Select the entire area and name the selection DATA.

[TABLE="class: grid, width: 158"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Region[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Sydney[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Brisbane[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Hobart[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Newcastle[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Perth[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Gold Coast[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Broome[/TD]
[/TR]
</tbody>[/TABLE]

2. Select the entire area and name the selection RCtable.

[TABLE="class: grid, width: 256"]
<colgroup><col width="64" style="width:48pt" span="4"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Region[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 64, bgcolor: transparent"]Feb[/TD]
[TD="width: 64, bgcolor: transparent"]Mar[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]97[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent, align: right"]34[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]47[/TD]
[TD="bgcolor: transparent, align: right"]51[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Total[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]91[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]177[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]125 [/TD]
[/TR]
</tbody>[/TABLE]

Let A1:D7 house the area for sales by region.

3. In B2 control+shift+enter, copy across to D2, and down:

=SUM(IF(ISNUMBER(MATCH(INDEX(DATA,0,1),IF(INDEX(RCtable,0,1)=$A2,INDEX(RCtable,0,2)),0)),INDEX(DATA,0,MATCH(B$1,INDEX(DATA,1,0),0))))

 

[blackmajik072]

Hi Aladin Akyurek,

Thanks so much for the quick response. I've followed your instructions - I did remove the space in INDEX(DA TA,1,0),0). Although I get "Not Available" (#N/A) error. I made sure to create the array formula by using the control+shift+enter keys.

What am I doing wrong?

Thanks.

 

[Aladin Akyurek]

Hi Aladin Akyurek,

Thanks so much for the quick response. I've followed your instructions - I did remove the space in INDEX(DA TA,1,0),0). Although I get "Not Available" (#N/A) error. I made sure to create the array formula by using the control+shift+enter keys.

What am I doing wrong?

Thanks.

That space is due to the board software.

Did you the naming I asked for correctly?

Did you apply control+shift+enter? That is, press down the control and the shift keys at the same time while you hit the enter key?

 

[blackmajik072]

I finally figured it out. It took me a while as the formula you gave me is a bit beyond my current level of skill (thanks for the development opportunity ). Turns out the problem was because I created my tables with headers. As a result the "month" wasn't found, hence the #N/A.

Thank you very much for the help!

 

[Aladin Akyurek]

I finally figured it out. It took me a while as the formula you gave me is a bit beyond my current level of skill (thanks for the development opportunity ). Turns out the problem was because I created my tables with headers. As a result the "month" wasn't found, hence the #N/A.

Thank you very much for the help!

You are welcome. By the way, the definitions I suggested include the headers.