Aggregating data with differing ranges

**Curver21** · 03-27-2013, 11:08 PM

Hello!

First off, I apologize if I'm somewhat vague. I couldn't come up with a better title.

I have a relatively large database with number of votes per voting district (I can share this data - it's not protected or anything). These voting districts are of course located in municipalities. I want a total of votes per municipality. The trouble is that the number of voting districts per municipality differs, so with my limited knowledge I am left in the dark on how to solve this. I'd just do it manually, but with more than 90k cases, that would be quite an ordeal.

I've attached the first 32 cases. Details:

A2-33 = municipality (number)
B2-33 = municipality (name)
C2-33 = voting district (name + postal code)
D2-33 = votes of two parties I have data of. So this number is X votes for the two parties per voting district
E2-33 = total number of votes (per voting district)

What I would like to have is:

F2-33 -> number of votes for the two parties per municipality (so I want the data of D2-33 aggregated per municipality number (A2-33))
G2-33 -> number of total votes per municipality (this data is now in E2-33 per voting district)

Many thanks if you could help me!

PS In the attached document I left some non-working formula in G2 and H2 by accident (it's irrelevant).

**FDibbins** · 03-27-2013, 11:38 PM

Hi and welcome to the forum

In G2, copied down, use this - it will extract unique entries from Col B...
=IFERROR(INDEX($B$2:$B$33,MATCH(0,INDEX(COUNTIF($G1:G$1,$B$2:$B$33),0,0),0)),"")
Then in H2, copied down, use this - it will add the votes for each entry in G...
=IF(G2="","",SUMIF(B:B,G2,E:E))

If you want to put these somewhere else, move them both, but you will have to adjust the "COUNTIF($G1:G$1" part.

Also, I just used the range you provided (down to row 33) adjust this down as as far as you need it to go

**Curver21** · 03-28-2013, 12:26 AM

Thanks for the quick response! I'll have to try this in 10/13 hours, because my Office cannot change language for some reason. And when I translate the function to Dutch, it won't work at all (just gives an error, highlighting the '$B$33,MATCH' section). But I'm sure it works, so thanks again!