< Previous lesson | Overview | Next lesson >
=SUMIF()
How to sum only lines meeting your criteria
=SUMIF() is one of those really powerful formulas, and is one that everybody who work with large lists should really get to know intimately.
With =SUMIF() you can sum all values in a column as long as the value on the same line in a different (or the same) column match a criteria you set.
Sounds complicated? Actually, it isn’t, believe it or not! Please go to the next step, and I’ll show how to use it.
Imagine that you have a list with these columns:
- Column A: Name
- Column B: Product
- Column C: Sales
Now, to sum horisontally and vertically, we can use the excellent formula =SUM(), right? Right.
But it gets a bit more complicated the moment we want to sum only Eddies sales, doesn’t it? We could of course split sort each column and create one list for each person and use =SUM(). It would give you the correct amount, but it would involve a lot of work, and, in the end, the possibility of for errors will increase. Imagine the lists change all the time, and contains 10 000 lines...
This is where =SUMIF() can be an excellent solution!
What we’ll do, is to enter any name in a cell, and let Calc sum all values related to this name.
The syntax is: =SUMIF([Column to be evaluated];[Criteria];[Column to sum if criteria is met])
[Column to be evaluated]
In our example, this will be column A. So, in our example, if the name is "Eddie" is found in column A, it will be summed, otherwise it will be ignored.
[Criteria]
This is the criteria to be fulfilled in [Column to be evaluated]. This can be a formula, a value or a cell reference. In this case, it refers to cell F3, where Eddies name is.
[Column to sum if criteria is met]
If the criteria in the first column is met, this column is the one to sum. In our example, that would be the column for Sales, column C.
Please note that [Column to be evaluated] and [Column to sum if criteria is met] can actually be the same column! You could for instance specify that all values greater than 10 in column C should be summed.
Here’s a nice colorization of the formula used in our example:
=SUMIF(A1:A11;F3;C1:C11)
You can now try to change the name between Eddie, Diane and Tor. Also, try to enter a name that’s not included in the list, just to see what happens.
This time we’ll ad the ability to sum by product in the same manner.
Instead of having the [Column to be evaluated] to be column A, we’ll use column B, as you can see above.
Try entering Oranges, Apples and others, and see what happens!