Use VLOOKUP or SUMIFS for regional/political party conditions

KC2MF

Board Regular
Joined
May 13, 2011
Messages
54
I am doing some research on Presidential elections and wish to sort the results by which region the nation is located in. Here is a look at the data as I have it presented. (the numbers are based on the 2012 Presidential Election)


<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody></tbody>
StateRegionDemRepIndOthTot
AlabamaSouth795,6961,255,92512,32810,3892,074,338
AlaskaWest122,640164,6767,3925,787300,495
ArizonaWest1,025,2321,233,65432,10015,5732,306,559
ArkansasSouth394,409647,74416,27611,0391,069,468
CaliforniaWest7,854,2854,839,958143,221218,35113,055,815
ColoradoWest1,322,9981,185,05035,54527,9562,571,549
ConnecticutNortheast905,109634,89912,5806,4051,558,993
D.C.South267,07021,3812,0833,230293,764
DelawareSouth242,584165,4843,8821,971413,921

<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>


What I would like to do is to run a VLOOKUP or SUMIF(S) (unsure of which one, or if these are the best) to sum the number of votes for each party in each region. On a separate sheet, here is how I'd like to allocate the votes. (the national results are incomplete but serve as an example; the Midwest and Northeast ARE complete). The regions are based on the Census allocation of each state.

Region2012
Dem29,452,006
Rep24,182,561
Ind504,996
Oth431,999
Tot54,571,562
MidwestDem15,790,893
Rep14,843,298
Ind317,825
Oth259,852
Tot31,211,868
NortheastDem13,661,113
Rep9,339,263
Ind187,171
Oth172,147
Tot23,359,694

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

<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>

Could anyone suggest to me what formulas I should use for this situation?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
A Pivot Table is a good solution.

As an example, with the data sample above you can easily achieve this


A
B
C
D
E
F
3
Region​
Dem​
Rep​
Ind​
Oth​
Tot​
4
Northeast​
905.109​
634.899​
12.580​
6.405​
1.558.993​
5
South​
1.699.759​
2.090.534​
34.569​
26.629​
3.851.491​
6
West​
10.325.155​
7.423.338​
218.258​
267.667​
18.234.418​
7
Grand Total​
12.930.023​
10.148.771​
265.407​
300.701​
23.644.902​

Row labels
Region

Values area
Dem
Rep
Ind
Oth
Tot

Hope this helps

M.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top