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

Region	Jan	Feb	Mar
East	25	97	34
West	12	20	22
North	34	13	18
South	20	47	51
Total	91	177	125

<tbody>
</tbody>

Region/City Lookup Table

Region	City
East	Sydney
North	Brisbane
South	Hobart
East	Newcastle
West	Perth
South	Melbourne
North	Gold Coast
West	Broome

<tbody>
</tbody>

Sales by City

City	Jan	Feb	Mar
Broome	10	15	14
Melbourne	20	47	51
Gold Coast	34	13	18
Sydney	11	43	12
Newcastle	14	54	32
Perth	2	5	8

<tbody>
</tbody>

 

[Aladin Akyurek]

City	Jan	Feb	Mar
Broome	10	15	14
Melbourne	20	47	51
Gold Coast	34	13	18
Sydney	11	43	12
Newcastle	14	54	32
Perth	2	5	8

<tbody>
</tbody>

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

Region	City
East	Sydney
North	Brisbane
South	Hobart
East	Newcastle
West	Perth
South	Melbourne
North	Gold Coast
West	Broome

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Region	Jan	Feb	Mar
East	25	97	44
West	12	20	22
North	34	13	18
South	20	47	51
Total	91	177	125

<colgroup><col width="64" style="width:48pt" span="4"> </colgroup><tbody>
</tbody>

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.