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.
Leave a Comment