0

I have a spreadsheet containing the following four columns, each with ~18,000 rows:

  1. Sub-region code: A geographic variable that has been used by my country's statistics bureau to create State and Federal regions.
  2. Region #1: A column of ~90 State regions that correspond to each of the sub-region codes above.
  3. Region #3: A column of ~60 Federal regions that correspond to each of the sub-regions.
  4. Population: The estimated population of each of the sub-regions.

I have provided a simplified version of this spreadsheet in the image below:

enter image description here

What I would like to do is create a list of unique Region #1 variables in one column, with a second adjacent column showing the unique Region #2 variables that overlap the Region #1 figures, and in a third column the total population within the overlapped Region #1 and Region #2 areas.

I can then manually calculate the percentage of the Region #1 population that is in each Region #2 area.

If possible, I would like a final product that looks similar to the (manually created) image below:

enter image description here

I am uncertain where to begin in order to perform this task, so would greatly appreciate any advice as to which function is best for such work.

Improve this question
2
  • I'm not exactly clear on what you're asking, but I'm pretty sure you can do it with SUMIF() or SUMIFS(). And this link explains how to list the unique regions.
    – Bandersnatch
    Dec 17, 2017 at 14:51
  • In the example image above I'm essentially wanting to determine what proportion of Albury's population (a state region) is present in two Federal regions (Farrer and Eden-Monaro). The regions have a common geography that is divided into very small sub-regions, so I need to add up all of the sub-regions that intersect both the State and relevant Federal region. I'm not sure if that clarifies things or makes it more complicated sorry!
    – Palaeologus
    Dec 17, 2017 at 22:12

1 Answer 1

Reset to default
1

Try to use the PivotTable to get the result:

Create a PivotTable based on your data source and drag the fields to specific area, drag the field Population twice to VALUES area as shown in the following picture:

Create PivotTable

Then Right click the Sum of Population2 and select Value Field Settings, select Show Values as tab and Select % of Grand Total:

% of Grand Total

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .