0

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with a text value like R3, R2, R1, D3, D2, D1, etc.):

enter image description here

What I want is the sum of the population of all states that fall under a specific category of political control. So what is the total population of all the "R3" states in 2019? This would mean the sum of the population for Alabama in 2019, Arizona in 2019, etc since these states all have political control of "R3". Same for the total population for "R2" states, "D3" states, etc. Basically, for each unique text value, I want the sum of all the cells below that text value for each year. Any idea of how I could calculate this or how I could rearrange my spreadsheet easily to make this more manageable?

  • pls share a copy of your sheet – user0 Apr 2 at 8:29
0

Try using an additional column, say J. Fill that with the following logic,

if (A2 = "R3" then A3 else 0)

Then sum the column. If you want to keep your spreadsheet pretty, then hide the extra column out of sight to the right, or on a different worksheet.

0

As for rearranging your spreadsheet, I recommend arranging it how you want to view it regardless of how you need to calculate the data. The answers by user0 and phv3773 seem to work, and I'm sure there are many more variations. user0's answer looks like it gets you what you want. I'd vote it up, but I don't have the status to do that yet.

0
  • for only 2019 year:

    =QUERY({FILTER(B2:B, ISEVEN(ROW(B2:B))), FILTER(B2:B, ISODD(ROW(B2:B)))}, 
     "select Col1,sum(Col2) 
      where Col1 <>'' 
      group by Col1 
      label Col1'Category', sum(Col2)'2019 population'")

    0

  • for all years:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(B1:H1&"♠"&{
 FILTER(B2:H21, ISEVEN(ROW(A2:A21)))&"♠"& 
 FILTER(B2:H21, ISODD( ROW(A2:A21)))}&"♦"), , 999^99)), , 999^99), "♦")), "♠"),
 "select Col1,Col2,sum(Col3) 
  group by Col2,Col1 
  order by Col1 desc,Col2 
  label sum(Col3)''"))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.