Using the Numbers SUMIF Function

The SUMIF function is one of the most useful functions in Numbers. You can use it to calculate the total for each category when you have a list of numbers and categories. The best way to use it is to create a second table and use one column in that table as the comparison value.

You can also watch this video at YouTube (but with ads).

Video Transcript: So I've gotten several questions recently about using Numbers to find the total of cells that match a certain condition. For instance, here's an example. You have a little list of expenses and you've got different categories like restaurant, market, home, and entertainment. You've got amounts. Say you want to find the total of all of the amounts for the category restaurant. So you've got this one, this one, this one. For market you've got this one, this one, etc. How can you do that?
Well, the trick is to use the SUMIF function. So let's create another table here. I'm going to hit Table and create another table. Let's shrink that down. We only need it to be two columns wide. I'm going to stick it right next to this one here. I'm going to call this By Category. It's going to be the Category and Total as the column headings. 
The first one we want to calculate is the restaurant total. So how do we do this? Well, we're going to use SUMIF. So I'm going to go into formula entry mode.  I'm going to hit the equals key which is the way I like to do it. Use SUMIF and I'm also going to search for SUMIF over here. I already have to look it up. Anytime you use a function and you're not really familiar with it just look it up here so you can see the details. I can see here a summary of it that its tests values, conditions, sum values. I can scroll more and I can see examples and information and everything. 
So for SUMIF the test value is going to come from column B here or the category column. Just click there in B and you can see it inserts it. I'm going to hit comma. Now what I want it to compare it to is the word restaurant so I'm going to do quote restaurant quote and a comma. Then I want it to take the values for adding up for the sum from column C. So I click C over here and it puts in amount because that's the column heading there. Then I'll close the parentheses there. I'll hit the green accept button and you can see it adds up all of the amounts that have the category of restaurant. 
Now a better way to do this, since I have the word restaurant here in the cell to the left, is instead of using quote restaurant quote in there I'm going to replace that with the link to this cell. So that saves me from having to have the word restaurant here and then have it in the formula as well. What's cool about that is I can create another row called market and I can copy this cell and paste it here. If I look at that formula now instead of A2 it's going to take A3. It advances since this is one cell down from the original place I copied from it's going to also move the value here one down. So it's going to be pulling the market instead of the restaurant category.
I can easily then create home and entertainment. I can copy either one of these two formulas and paste it in each spot. So I get the sums from here. So anytime I add a new category over here all I've got to do is to just add it once here on the left and copy and paste that same formula over and I get my totals done automatically. I don't have to retype all that stuff in and create a new formula each time. 
So that's how you use SUMIF. It's extremely useful. It's probably one of the most useful and used functions in Numbers or Excel. It's important if you use spreadsheets a lot to know how to do it. It solves a lot of problems for a lot of people.

Using the Numbers SUMIF Function

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information