COUNTIFS function is one of the statistical function that counts the number of cells in a range. Together with IF function, you can use them to summary count with two criteria

**Generic Formula**

**=****COUNTIF****($A$1:A1, ****value****)**

If you have data and you want running count of occurrence in the list, the COUNTIF function serves you the best. Check out this example with car models.

*Figure 1. Example 1 of COUNTIFS Function*

From the screenshot, the formula in C6 is:

**=****IF****(B6=”****subaru****”,****COUNTIF****($B$6:B6,”****subaru****”),””)**

**Here’s How Formula Works**

From the inside of the formula, the COUNTIF is set up to calculate “subaru” cars that are in column B:

`=COUNTIF($B$6:B6,”subaru”)`

**…**you will notice that this a mixed reference from the mere fact that relative and absolute addresses. **$B$6 is locked while the B6 relative. **However, it creates an expanding. Therefore, when copying the formula down, the first cell in the reference will be locked but the second reference will expand thus including every new row. On every row, COUNTIF will be counting the cells that are to “Subaru” and that is how a running count is achieved.

The IF function controls when COUNTIF fires. Note that COUNTIF function only generates a count when the value is “subaru”. If that is not the case, IF returns an empty string (“”).

**Running Count of Every Value**

If want to generate a running count for every value of all car models in column B, this is the formula you should use:

**=COUNTIF($B$6:B6,”B6”)**

..where the generic criteria should be (**$A$1:A1, ****“A1”)**

## Leave a Comment