Introduction
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to sum every third rows across multiple columns. Let’s get started
Sample Data
In the caption below, we have our sample data we will use to achieve the technical calculation. Note, our data is formatted in Excel Table and we provided Sales_Data as the Table Name
Calculation
We want to sum the first three Quarter 1 to 4 sales from our main table, we expect Abiola David Q 1 to 4 data which is in row 4 excluding the headings in row, to be summed and delivered in H2. Same thing for Yvonne Matthew, Mansoor Ali and so on.
- In cell H2, execute the formula: =INDEX(Sales_Data[Q1]+Sales_Data[Q2]+Sales_Data[Q3]+Sales_Data[Q4],ROWS(H$2:H2)*3)
Sales_Data[Q1]+Sales_Data[Q2]+Sales_Data[Q3]+Sales_Data[Q4] represent the values for Q1 to 4 which is the array we want to sum and returned.
ROWS(H$2:H2)*3 represent the row number argument and the *3 represent third rows each
- Click Enter and copy down the formula
From the above caption, we can see the summed values for each of the third rows
You can watch the video below