COUNTIF and SUMIF Formulas in Microsoft Excel

Excel offers two simple built-in formulas to tackle counting and summing in rows or columns based on criteria that you establish. The formulas discussed in this article are COUNTIF and SUMIF.

COUNTIF

This formula will analyze data in a range consisting of single row or column, or adjoining rows or columns. For example, you could analyze data in the column defined by A1:A20, or you could be looking at A1:B20. Either range is acceptable to use for the COUNTIF function. The form of the formula is:

=COUNTIF(Range,Criteria).

Let us say, for example, that you are interested in determining the total number of sales in a region, with regions being defined as North, South, East and West, and you have daily sales for each region for a five-day period.



The first step might be to determine how many sales over the five-day period exceeded 30 units per day. Further assume that our region names are in the range C6:C9 with corresponding unit data in D6:H9. The formula to find unit sales over 30 would be =COUNTIF(D6:D9,">30"). This will give you the correct answer. Note that in this function, the criteria portion needs to be in quotes. You could also have that linked to a cell, such that the formula could be =COUNTIF(D6:D9,A6), where the cell A6 would have =">30" in it. Sometime, linking to another cell provides more flexibility to a financial model, especially is you are doing a quick sensitivity analysis.

The COUNTIF function has some flexibility to count based on wildcards, like creating criteria that is "*py" to find all words that end in "py" or containing "py" would be "*py*" and so on. There are advanced criteria that will be covered in my blog, but for now, we want to keep it simple.

The major drawback to the COUNTIF function is that is can only take a single criterion to analyze. So, if you wanted to determine all of the unit sales over 30 AND within the North region, you would have to do a formula like the following:

=SUM(IF(C6:C9="North",(IF(D6:H9>30,1,0),0)).

In this case, you would have to use Ctrl+Shift+Enter to get the right answer (this is array formula entry, which is more advanced than this article intended, but is necessary for your understanding). This formula will tell you how many days in the five-day analysis in which unit sales exceeded 30 in the North region.

SUMIF

The SUMIF formula is similar in its constraints to the COUNTIF formula, only being able to assess one particular criterion. The form of this function is:
=SUMIF(Range,Criteria,Sum_Range).

Let us assume that you need to know the total of all unit sales in the five-day period for days in which the unit sales exceeded 30. The formula would be:

=SUMIF(D6:H6,">30",D6:H6).

This literally breaks down into looking at the range defined by D6:H6, finding all values greater than 30, and then summing those values within the range of D6:H6. This seemingly simple example is only meant to convey the context by which you would use this formula. A more likely scenario could involve a long list of salesforce member names, defined by the range of A1:A200, with the results of last year's sales by product in B1:B200. If you wanted to know the total sales of all products by name, the formula would be:

=SUMIF(A1:A200,"Name of Key Employee",B1:B200).

This is the more likely use of the SUMIF formula. Once again, it bears mentioning that the shortcomings of this formula minimize its overall usefulness. In simple database settings, it will work fine, but if you have a large database of information such as sales, profits, units sold and inventory, all by salespersons, you are likely to want to better understand the array formula entry as it will be more flexible and easier to manipulate to provide a variety of the desired results.