where value is the named range E5. The result is a running count of the 4 cells that contain “blue”, since E5 contains “blue”.
Basic count
Normally, the COUNTIF function is given a range and criteria like this: Since the range B5:B16 contains 4 cells that contain “blue”, COUNTIF returns 4. The formula above works well when you want to single count for one value in a range. However, to get a running count, we’ll need to adapt the formula to use an expanding range.
Running count with expanding range
An expanding range is a range with one side that is absolute, and one side that is relative. In this case, we lock the first reference to B5, and leave the second reference relative like this: Notice that the left side of the range is locked ($B$5) and the right side is relative (B5). This is a special kind of “mixed reference”, since it contains both absolute and relative addresses. Because the first reference, $B$5, is locked, it doesn’t change. However the second reference, B5, changes when the formula is copied. The result is an expanding range. Dropping the expanding range back into the COUNTIF function, we have this formula: As the formula is copied down column 1, the range “expands” at each row. You can see the result of the formula above in the screen below:
Notice we get a count on every row. However, the count only increments when another “blue” value is encountered.
Removing extra counts
Because we only want to create a running count for one specific value, the next step is to remove the counts that appear next to other values. To do this, we nest the COUNTIF function inside the IF function like this: This is the formula entered in cell C5 of the example shown. Notice we have now swapped the hardcoded value “blue” with the named range value (E5). The named range is simply for convenience. It automatically acts like an absolute reference, and it makes the formula easier to read and write. At each row, the logical test inside of IF tests the value in column B to see if it is equal to the named range value (E5), which contains “blue”. If so, the COUNTIF formula explained above is executed, and returns the current running count for “blue”. If the value in column B is not equal to “blue”, the IF function returns an empty string (""). This effectively removes the count for all other values. The final result is a running count for cells that contain “blue”.
Other values
In the attached worksheet, the named range value (E5) is set up to provide a dropdown menu with data validation. If we select a different value, the running count formula immediately returns a new set of running counts. The screen below shows the result of selecting “Green” in E5:
Running count for every value
To create a running count of every value that appears in column B, you can use a generic version of the formula like this: As this formula is copied down the table, it returns the running count for every value in column B.
Excel Tables
In an Excel Table, formulas that use standard expanding references can become corrupted when rows are added or removed. You can work around this problem by using a structured reference to create an expanding range like this: When entered in cell C5, this formula will return a running count for every value in every row. To return a running count for just one value as in the original example above, nest the formula inside IF like this: For more information, see the example here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
