# Summing every other row depending on the classification of the row above

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.): 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 '19 at 8:29

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.

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.

``````=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)''"))`````` • – user0 Apr 4 '19 at 2:42