Method of summing every other line.(Microsoft Excel)
If you want to sum every other line, you can simply open and select one line at a time if the table has little data, but if the table is large, the work becomes more difficult and mistakes are more likely to occur.
Here we introduce an efficient way to achieve this.
Steps
As an example, a table like this is used to calculate sales-only and cost-only totals, respectively.
Create intermediate calculation cells.
Use the MOD and ROW functions to create an intermediate calculation cell for the following formula
=MOD(ROW(),2)
The ROW function gives the number of rows, and dividing by 2 gives 0 for the sales row and 1 for the cost row.
Sums with the SUMIFS function.
Setting the criteria to 0 results in total sales, and setting the criteria to 1 results in total costs.
=SUMIFS(D2:D9,E2:E9,0) =SUMIFS(D2:D9,E2:E9,1)
Do it in total cells only. (Array formula or Spill)
Use this when you do not want to create intermediate calculation cells for any reason.
Use the SUM, IF, MOD, and ROW functions to create formulas in the cells.
=SUM(IF(MOD(ROW(D2:D9),2)=0,D2:D9,0))
=SUM(IF(MOD(ROW(D2:D9),2)=1,D2:D9,0))
If your version supports Spill, just press [Enter] to confirm the formula. Otherwise, use [Ctrl]+[Shift]+[Enter] to finalize the formula to make it an Array formula.
---