In our first post, we looked at the power of SumIf’s. Here, we will look at another way to summarize the same data, but by location code.
We add our three location codes from the data above in Col. A. In Col. B the formula “=SUMIF($A$2:$A$37,A41,$B$2:$B$37)“. In this example, cell A41 has our location code “001”. Copy/paste the formula. We add a total to check against the sum of all data.
In Excel, there exists a powerful formula for summarizing data in your general ledger. It’s “sumif”. Here’s a few ways that I use it.
In our sample data, we have a three digit location code, an amount, and the month.
Let’s say we want to summarize the transactions by month. Below the data, I will put the months in column C.
In column B, beside the month. We add the formula “=SUMIF($C$2:$C$37,C41,$B$2:$B$37)” The $ are to lock in the columns and rows so that they stay fixed as we copy and paste. The formula is in three parts. The first is the data with the months, second is the month we want summarized,and the third has the amounts. Then copy and paste down to all 12 months.
Add a total formula to check that the totals match our total of all the data.