Power of SumIf’s (Part 1)

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.