Sumif and Countif Formulas in Excel
- 0 Comments
There are many times when analyzing large amounts of data when you might want to sum or count a column or range based on certain criteria. One example is if you have a database of products you have sold, you might want to know the sum of profits for a particular product – which is where the sumif formula would come in handy. Or possibly for the same example you want to know how many instances of a particular item you have sold, which is countif’s formula specialty.
Let’s start by taking a look at the formulas:
COUNTIF(range,criteria)
SUMIF(range,criteria,sum_range)
Both the Countif and Sumif formulas have very similar criteria. When you are using the Countif formula you need to the it the range of cells that you want evaluated with a certain criteria. Sumif is much the same way with one exception, the sum_range can be different from the range you are evaluating with a specific criteria. We well show numerous examples below to illustrate how the formulas work and their differences.
Examples
Example 1: =COUNTIF(C2:C8,C2) will return 3. In this example, we are telling excel to count the number of times orders were placed for “Red” products – 3 instances were found in lines 2, 7 and 8.
Example 2 : =COUNTIF(D2:D8,”>10″) will return 3. In this example, we are telling excel to count the number of times orders were placed for that were greater than $10 (notice the item criteria needed to be placed in quotes this time since it was not a cell referent) – 3 instances were found in lines 4, 7 and 8.
Example 3 : =SUMIF(A2:A8,111,D2:D8) will return $14. In this example, we are telling excel to sum the prices (column D) of the any product in column A that equal “111″ – 2 instances were found in rows 2 and 5 which summed values $10 and $4.
Future Considerations
One of the next sections we will be adding to ExcelHints.com is there may come a time when you want to sum a range based on more than one criteria. In these cases you want to use the Ctrl-Shift-Enter (or CSE) formulas which use arrays to calculate. Because these get more involved than the straight forward Countif and Sumif formulas, we will save them for their own topic.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.