## Explanation

In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. 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. Also included below are links to a pivot table option and a dynamic array formula option. Both of these alternatives can automatically extract the values to count and generate the counts at the same time.

### COUNTIF function

The COUNTIF function takes two arguments: a* range* of cells to count, and the *criteria *to use for counting. For example, to count cells equal to "red" in a range, we could use COUNTIF like this:

```
=COUNTIF(range,"red") // count "red" cells
```

In this example, we want a count for 3 colors, so we have manually created a small table that lists all colors in column E. This allows us to use the color names in column E both for labels, *and* for the criteria that goes into COUNTIF as the second argument. The formula in cell F5 is:

```
=COUNTIF(color,E5) // returns 3
```

where **color** is a named range for cells C5:C16. As the formula is copied down, it returns a count for each of the three colors shown in column E. Notice because we are testing for equality, we don't need to use any logical operators in *criteria*. We can simply use the value in cell E5 directly for *criteria*.

By default, named ranges behave like absolute references and do not change when copied. This means the reference to **color** does not change, while the reference to E5 is relative and changes at each new row. Alternately, we could use an absolute reference instead of a named range like this:

```
=COUNTIF($C$5:$C$16,E5) // absolute address
```

The formula above will return the same results.

### Pivot table solution

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 since it will automatically expand to include new data.

### Dynamic array formula solution

Now that dynamic array formulas are part of Excel, it is possible to build a dynamic summary count with formulas, including an all-in-one formula. Like a pivot table, a dynamic formula will automatically expand to include new data. Unlike a pivot table, formulas recalculate automatically and do not need to be refreshed.