Power of SumIf’s (Part 3) – Wild cards

Let’s say that you have list of account numbers and amounts.

Let’s assume that all the accounts that start with 4190 are “Admin Expenses”, and all the accounts that start with 4570 are “Maint. Supplies”. There is a way to summarize these with the sumif formula, and wild cards. Here’s how.

Below our list add 4190??, 4570??, and 4580??. The question mark character is our wild card.

In the C column…to the right of our category descriptions, enter the formula “=SUMIF($A$2:$A$17,A20,$C$2:$C17)

Remember to use the $ in the formula so that when you copy it, it keeps the ranges intact.

The final result…with total added to match our total above.


Power of SumIf’s (Part 2)

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.


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.