I am fed up hearing about the PLP’s 9m votes mandate. A large number of these 9m votes will have voted for Labour candidates that lost. So I decided to calculate the number of votes cast for the PLP. So something for both political geeks and excel nerds. Here’s how I did it.
First I obtained a results file from the Electoral Commission. There is no good reason to cache this, and many bad ones. Their URL is
There is a risk that they’ll remove the link but I’ll solve that problem another day. The tab, helpfully named ‘Results for analysis’, is what we need. The Constituency meta data is in columns a-i, column j is blank, and columns k -eq hold the party results. I have inserted anew column at column k, so the results are held in L – ER.
- New Column J, =MAX(L2:ER2), displays the winning result, which I called Winner
- New Column K, =INDIRECT(ADDRESS(1,SUMPRODUCT((L2:ER2=[@Winner])*(COLUMN(L2:ER2))))), for the non title first row where @Winner is J2, see above, which I called winning party. The ADDRESS function returns row 1, i.e. the column title cell, of the column containing the max votes. INDIRECT resolves the value of the column header. See here… for advice on using the address function for this purpose, and here… for advice on the indirect fucntion.
See the image below, which illustrates the analysis.
I can now make a pivot table using the winning party and the sum of the winning votes aka winner, remembering to add Labour and the Co-Op votes.