Exploiting Excel

There are a few side projects I do just to keep my card-carrying credential as a nerd. Two Excel functions I discovered that has changed the way I use Excel. This is all about managing data under specific conditions.

Most people probably know how to SUM() a block of cells, but what if I only want to SUM certain cells within that block when those cells meet a certain condition, go check out SUMIF() function.

Along the line of conditional sum, what if we want to change the location and size of the block of cells without going back to modify our existing SUM function? For example, there's a list of charges to my bank account, normally, my monthly statement lists all transactions and total sum of what happened within that period. What if I want to sum transactions across an arbitrary set of dates, for example, only the 2nd week of the month? This is where OFFSET can be used in conjunction with SUMIF. OFFSET function allows me to determine the origin of the offset, how much to offset to (i.e. where to start) and the size of the offset (i.e. where to stop). So instead of setting a fix block / range of cells within SUM, use the OFFSET within the SUM function.

SUMIF and OFFSET provide the freedom beyond the normal SUM function or any function that takes in a block or range of cells. SUMIF allows for conditional sum, and OFFSET gives me the flexibility of the location and size of the block of cells for manipulation within Excel.