In a previous post I talked about how to SUM cells using Excel’s SUM function. This is a great way to sum cells quickly, but did you also know
you can sum Excel cells based on a criteria you specify?
Looking at the image opposite, you could actually sum only the cells that contain a value greater than 20, or any other numerical value you like. The formula is highlighted in the green circle, and values greater than 20 are shown in yellow.
Using the SUMIF Function
To get the answer of 66 as shown in the image in cell B10, you need to type the following
Let’s break it down into it’s three parts.
Part One – =SUMIF(B2:B9)
Here we are telling excel we want to sum the range of cells selected (B2:B9) if they meet a criteria we later specify in part two.
Part Two – ,”>20″,
This is where specify the criteria on which to select the cells to sum. Here I have told excel to only sum cells that contain a value greater than 20. The greater than symbol, > , could also be reversed using, < , to say less than. If you only wanted to sum cells with a specific number then you do not need the quotation marks ” ” at the beginning and the end of this section.
Part Three – (B2:B9))
Finally, the section specifies the range to be summed. Generally this would be the same as the range specified in part one. Note the second closing bracket at the end. This is required because two opening brackets were used earlier on in the formula.