Sometimes the way people wish to format information in a spreadsheet makes it difficult to create elegant formulae. As an example, I was recently working with a spreadsheet that listed monthly expenses for a series of different departments. The different expense types were the column headings, and in each department section there were 12 rows for the months and a totals row. This set of 13 rows was repeated down the page.
The task was to create a set of grand totals at the bottom showing the monthly totals across departments and an overall grand total. Assuming ‘January’ was in row 2 to allow for the headings, and there were no blank rows, then each January would be in row 2+13+13+13 etc. We’ve hidden some of the rows in the following example:
Of course you could create the formula as =C2+C15+C28 etc but this is tedious to create – especially if you had dozens or even hundreds of departments. It would also require manual updating as new departments were inserted.
One solution is to use the MOD() function in conjunction with the ROW() function. ROW() returns the row number of the current row, or any reference entered as an argument. MOD() returns the remainder after a division. So MOD(2,13) returns the remainder of 2/13 = 2 and MOD(15,13) and MOD(28,13) also return 2.
If our January total cell is say C41 then the ROW() function used without any argument in that cell will return the row number of the current cell – 41. MOD(41,13) is 2. So we would want to sum all rows where MOD(ROW(x),13)=MOD(ROW(),13).
Enter the ubiquitous SUMPRODUCT() function and a bit of Boolean algebra:
=SUMPRODUCT(C$2:C$40*(MOD(ROW(C$2:C$40),13)=MOD(ROW(),13)))
The 39 steps…
Because SUMPRODUCT() treats its arguments as arrays, it performs 39 separate calculation steps. First it multiplies the value in C2 by whether MOD(ROW(C2),13)=MOD(ROW(),13) where ROW() returns the number of the current row, 41. IF this is ‘TRUE’ then the result is whatever is in C2 times 1, if FALSE then C2 times 0. Calculation 2 is C3 times whether MOD(ROW(C3),13)=MOD(ROW(),13) and so on up to C40, The results of the 39 separate calculations are then summed to give the total of the values that meet the criteria set. If you’re not used to SUMPRODUCT() and arrays then this might seem somewhere between slightly tricky and complete gibberish. There’s an attempt to explain it all in a bit more detail here:
http://www.ion.icaew.com/itcounts/18779 .
Clever as SUMPRODUCT() and arrays can be, because they process many calculations in a single formula, if you work with many formulae and large ranges then calculation can be slow. As usual with Excel there are other ways of achieving a similar result and in some circumstances DSUM() can be a better alternative. There’s a summary of both methods here:
http://www.ozgrid.com/Excel/sum-every-2nd-nth-cell.htm.
In this particular example, if you can rely on the consistency of the typing of the month names then SUMIF() could be the simplest alternative: =SUMIF($B$2:$B$40,$B41,C$2:C$40). Although of course this doesn't total every nth row, but every cell in C2:C40 where the adjacent cell in column B = the contents of cell B41.