Generic Formula of Count Items in List Function
If you want to quickly count values or items that appear in a table, the Excel COUNTIF can be used to do this.
The COUNTIFS function is designed to take a pair of range/criteria, and when all the given criteria match, it gives a count of all values.
Figure 1: Example showing how to use the Excel Count Items in List function to count similar items
In the above example, the formula used in E11 is:
The example above contains two arrays or criteria sets.
In the first pair, the array is C: C (this is entered as a reference to a complete column), also the criteria is C11. Automatically, this pair will return a value for every count of value in column C.
In the second pair, the array and the criteria are D:D, criteria are D11. Automatically, this pair will return a value as it counts every value in column D.
From the above example, since both pairs are found in identical COUNTIFS function, they connect values in column C and those in Column D together and the Excel COUNTIFS function automatically will produce a count of each C/D combination that is found in the table.