Exceljet

Quick, clean, and to the point

Summary count with COUNTIF

Excel formula: Summary count with COUNTIF
Generic formula 
=COUNTIF(range,criteria)
Explanation 

When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by category, color, size, status, etc. The COUNTIF function is a good way to generate these kinds of totals.

If you have a limited number of values to count this is a good solution. However, if you have a large list of values that will change over time, a pivot table is a better option.

Example problem and solution

In the example shown, we have a small set of order data. All orders are for T-shirts, which come in 3 different colors: red, blue, and black.

To the left, we are using COUNTIF to provide a breakdown by color. The formula in cell G5 is:

=COUNTIF(colors,F5)

where "colors" is a named range for cells D5:D11. We are using a named range in this case to make the formula easy to copy down the summary table. Alternately, you could use an absolute reference like so:

=COUNTIF($D$5:$D$11,F5) // absolute address option

How this formula works

The COUNTIF function takes two arguments: a range of cells to count, and the criteria to use for counting. In this case we want to count by color, so we have set up a small table that lists all colors in column F. This allows us to use the the color names in column F both for labels, and for the criteria that goes into COUNTIF as the second argument. 

In this case, we are testing equality, so we don't need to use any logical operators. We can simply enter the named range "color" for range, and a reference to the adjacent cell in column F for the criteria.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

Knowing your site is the best thing that ever happened to me since I started using excel. - MJM
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course