Using COUNTIF to Find a Running Count of an Occurrence in a List

We can do a running (continuous) count of an occurrence in a list by using the COUNTIF function in combination with the IF function. The steps below will walk you through the procedure.

Figure 1: Using COUNTIF to Find the Running Count of an Occurrence in the List

Setting up the Data

  • We will name Cell A3 as Laptops
  • We will input the names of laptops into Column A from Cell A4 to Cell A9
  • We will use Column B for the count and name Cell B3 as count

Figure 2: Setting up the Data

General Formula

  • =IF(cell containing the item="item",COUNTIF($A$4:A4,"item"),"")

Formula  

  • =IF(A4="Apple",COUNTIF($A$4:A4,"Apple"),"")

Using COUNTIF to Find the Running Count of an Occurrence in a List

  • We will insert the formula above into Cell B4

Figure 3: Using COUNTIF to Find the Running Count of an Occurrence in a List

  • Now, we will press ENTER

Figure 4: Result of COUNTIF in Finding the First Count of an Occurrence in a List

  • Now, we will use the drop-down feature to get the continuous count of APPLE in the list from Cell B4.

Figure 5: Using COUNTIF to Find the Running Count of Apple in the List

Explanation

We can create a running count of an occurrence in a list by using the COUNTIF function with a mixed reference to get a running or continuous total for an item. The mixed reference is present within the COUNTIF function; COUNTIF($A$4:A4,”Apple”). The dollar sign locks the left side of the range ($A$4:A4) and leaves the right (A4) relative. This creates an expanding range so that once the formula is copied, the first cell in the reference will be locked and the second reference is able to expand to accommodate each new row. The COUNTIF then counts the number of cells in the range that are equal to APPLE with the result being the running count.

Whenever COUNTIF fails to generate a count, IF returns an empty string (“”).

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

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