Today's lesson is to allow you to sum a range of cells based on criteria you specify. This is accomplished with the sumif function. This can be a very useful function once you get to know it.
The syntax for this formula are as follows: sumif(range,criteria,sum rang)
Range - This is the range that your criteria are listed in. This can be the same as the range you are summing, or not.
Criteria - this is the cell that contains your criteria.
Sum Range - This optional range is the range you want to sum, based on the whether the criteria, and your prior range, result in a "TRUE" result to the test.
This is very simple once we walk through an example. I will continue using the fruit we were using yesterday.
Here I've got my inventory of fruit on hand (Don't mind the brown apples, those are spoiled). Lets say I wanted to use the sumif function to sum all of the Apples on Hands.
My formula would be: =sumif(B4:B10,"Apple",D4:D10)
The range I am looking at is in column B. I want the answer in column B to match "Apple". I could replace the hard-typed "Apple" with a cell reference, and that cell could contain the word Apple. This would let me quickly select the fruit I wanted to sum. Finally, the D column is the area I want to sum.
Less practical for this example, but often useful; lets say I wanted to sum everything that was greater than 3 (I told you it wasn't practical here). Instead of using all 3 pieces of the formula, I can just have my formula set to: =sumif(D4:D10,">3") . Now I've got the criteria range, and the sum range, as the same, and I want to count everything that is greater than 3.
Unfortunately, sumif will only work using one set of criteria. If you want to use the same logic for more than one criteria, the formula gets a bit more complicated. This will be discussed in Part III of this series.
No comments:
Post a Comment