I use a spreadsheet for bookkeeping and indeed other things. This article is about understanding how this solution works, it is not about the syntax which is in the Excel help screens.
My file has what I call “ledgers” each comprised of three columns: the amount of the transaction, the running total (i.e. bank/credit card/payables etc balance) and a column for any notes (most frequently I note the balance per the statement when reconciled, so we know we are error free (apart from compensating errors of course)).
Now then, the best (i.e. simplest) way to add up numbers in a spreadsheet is usually the SUM formula. This doesn’t work when we only want every 3rd column’s figure. So until today I was forced to use the +d3+g3+j3+etc arrangement. This really assaults my spreadsheet sense of how to avoid error prone files. Of course the usual double entry controls also apply, but finding the source of errors in such a design can be time consuming and reduces my confidence in the file, so I have (had) to validate it more often.
The magic ingredient? The functions are SUM IF MOD OFFSET and COLUMN all combined. Excel help is good for explaining the syntax of each of these and indeed you can easily google “Excel sum every Nth” where this is explained at length and in detail by many excellent folk. My focus is on this practical application and how it seems to work behind the scenes.
Where my ledger columns span Columns P to AD, Here’s the formula entered at AE2:
=SUM(IF(MOD(COLUMN(P2:AD2)-1,3)=0,P2:AD2))
Note the -1 (minus 1) is NOT shown in the internet examples I found. It’s needed because there is a catch in all this. Tip: to understand how this works copy each section of the formula and see what results it creates. I will explain from the inside out, starting with COLUMN.
COLUMN is the range of columns containing my data in one row. Minus 1, we will come to this -1, you may or may not need this.
I would expect the COLUMN result to be a count of the number of columns, but it is not that at all. This took quite some mental adjusting for me. The result is NOT just a single number/total of cols. It is an ARRAY. This means it creates a list (in memory, you can’t see it, so like a calculator with memories) of each column’s number. You can see it if you just use the COLUMNS function alone. So that there is a real twist x 2. First the array and second its results are not on view to us humans when used in this way.
Here’s the trick: MOD gives zero when one number divides perfectly by another. E.g.15 divided by 3 is 5. No remainder, the remainder = zero. If I want to include every third column all I need do is divide by 3 and use each column number that has no remainder, i.e. Select for processing where MOD of column number divided by 3 = 0.
Twist: the way Excel works in this is it iterates through the unseen ARRAY (list of COLUMN results). The formula operates on every instance of the MOD COLUMN = 0 . It’s like a list of the formula each one changing the column number plus one, but it’s all in one cell. This is really akin to programming rather than traditional spreadsheets, not being able to see directly, but having to imagine. We are experiencing 3D operations in our 2D sheet. To try it, just list the formula, remove the COLUMN function and replace with a fixed number and add one for each copy down.
I can see why internet writers do not try to explain this, but to understand how it works so as to be able to construct my own solutions and you yours, we need to know. I am explaining what is not in the help screens and not what is.
So, to summarise to here: IF MOD column number, divided by three (in my case for every third column) has remainder 0, THEN add the figure in that column to our addition register (same as the calculator memory). Go to next iteration (i.e. check the next column along) until all columns have been considered and included (so value amount added to register) or rejected (reject where column number does not divide perfectly by 3). It is the SUM function that is doing the adding to register process, when passed the amount by the IF function.
A CATCH!
My desired column’s number maybe 3 (i.e. column C) and that divides by 3 nicely. Remainder = 0
My desired column’s number maybe 4 (i.e. column D) and that divides by 3 badly — meaning it has a remainder
This is what that minus 1 is for. The MOD formula will iterate through the COLUMNS range, but to make the columns divide by three where I so desire it I had to subtract one and happily it also iterates that deduction with every column number result. The (practically) secret knowledge in this is recognising when ARRAYS are being used, and Excel help is good at giving the heads up, as long as we know what it means when it says the result is an array (a list). If it’s an array result, some functions will iterate through each result others not. Knowing which is which is another discussion, and SUM does.
