Hello Friends,
In this topic, I will tell you the trick/formula to the sum of every nth row and column of a range in excel.
If you know about the OFFSET function will easily understand this example. If you want to learn about the offset function, then follow the offset function.
In the above example, I have taken a monthly sales record of the items of a computer store. Now I want to make a report to find the quarterly sales of each item. To get the result I have used a formula.
To add the value of 3 different months, mention the height (1) and Width (3). By changing the value of height and width you can add as many numbers of rows/columns as you want.
Similarly, you can get the result if your monthly data in rows wise by using the formula
In this topic, I will tell you the trick/formula to the sum of every nth row and column of a range in excel.
If you know about the OFFSET function will easily understand this example. If you want to learn about the offset function, then follow the offset function.
In the above example, I have taken a monthly sales record of the items of a computer store. Now I want to make a report to find the quarterly sales of each item. To get the result I have used a formula.
=SUM(OFFSET($C6,0,(COLUMN()-COLUMN($P$6))*3, 1,3))
As we know, to calculate the first quarter, we have to add the first three months of sales. The sales data for the first three months is in the same row and 3 different columns. So in formula row is 0.To add the value of 3 different months, mention the height (1) and Width (3). By changing the value of height and width you can add as many numbers of rows/columns as you want.
Similarly, you can get the result if your monthly data in rows wise by using the formula
=SUM(OFFSET(C$3,(ROW()-ROW($H$3))*3,0,3,1))
No comments:
Post a Comment