When analyzing data, sometimes you may want a quick count of certain values (especially if those values occur repeatedly), including how many times an item occurred and a running count next to the value. While many people are familiar with and use COUNTIF and IF functions separately, a “nested” COUNTIF function inside an IF function can be very useful in complex situations.
In addition to this technique of using Nested functions, Conditional Formatting may also be applied to make those values or counts stand out in color, to see what is happening in the data.
(FYI: CountIF and IF functions are taught in our online Excel level 2 Intermediate course)
Consider a scenario mentioned above where a certain category or value is repeated many times in a data set. Let’s say that you want to count how many times the value appears as well as keep a running total of the count. In addition to the running total, you want to display what number in the occurrence that the value is in the data set.
The following example demonstrates the use of a Nested COUNTIF function inside an IF function, to perform the count and the running total.
The question: How many times does the Item value “D” occur in the data set? Also display what number of occurrence the value is next to the Item.
Standard Syntax for CountIF function: =CountIF(Range,Criteria)
Standard Syntax for IF function: =IF(Logical Test, Value If True, Value If False)
Nested COUNTIF inside an IF function: =IF(C8=$F$8,COUNTIF($C$8:C8,$F$8),””)
The Nested CountIF function calculates the value of cell C8, and the number of the occurrence next to value in C8. When the formula is copied down, the C8 count will be changed to value in C9 (relative reference).
The IF function will test for the value in C8 to the value entered in cell F8 and if it matches the value in C8 the count is performed. If it does not match the value in cell F8, then it will produce a value of blank/Null (represented by the double quotation marks in the IF function). If the value in F8 is changed to another Item value, then the count is performed for the new value (This is a dynamic way of doing counts by the user).
The Value of “D” occurred 3 times and the third occurrence was in cell C17.
**NOTE: In addition to the count performed by the CountIF and IF functions, the yellow highlight is performed by using conditional formatting. Note the item value “D” is counted and highlighted in yellow.