Here, we will learn how to use the Excel statistical function: COUNTIF. Later, we will also learn how to count checkmarks and cross marks using COUNTIF and the SWITCH function.
COUNTIF function
The Excel COUNTIF function returns the count of cells in the given range that meet a single condition. The return value of the function is always the numeric value representing the cells counted. For example, to count the cells that contain the letter "A" in the range A3:A7, use COUNTIF like this: =COUNTIF(A3:A7,"A").
COUNTIF takes two arguments: range, criteria, all of which are required. The first argument range will take the range of cells in which to count the cells if the given criteria is met. The second argument criteria will take the condition that will be tested against each of the cells in the given range. If a cell passes the condition, it will be counted.
Counting checkmarks and cross marks using COUNTIF with SWITCH
Suppose you have a dataset (shown in the image) containing some tasks. You have remarked some as "Done" and some as "Pending". You create a helper column "Status" and want it to display a checkmark for the completed task and a cross mark for the in-completed task.
The SWITCH formula in C12, copied down, is:
=SWITCH(B12,"Done","P","Pending","O","?")
When the value in B12 is "Done", SWITCH will return "P", when "Pending", it will return "O". When no match is found, it will return "?".
To convert "P" and "O" to checkmark and cross mark respectively, select the range C12:C19 and apply the "Wingdings 2" font.
To count the number of checkmarks, the formula in B24, is:
=COUNTIF(C12:C19,C12)
COUNTIF returns 5, since there are 5 cells in the range C12:C19 that contain the checkmark.
To count the number of cross marks, the formula in B25, is:
=COUNTIF(C12:C19,C14)
COUNTIF returns 3, since there are 3 cells in the range C12:C19 that contain the cross mark.
You have successfully learnt how to use the Excel statistical function: COUNTIF, and how to count checkmarks and cross marks using COUNTIF and the SWITCH function! I hope this post helped you.
