< Go Back

Running count of occurrence in list

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”)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar