Count values in range of columns if appear in array in Excel formula

The basic idea of counting values in a range of columns if they appear in an array is to use the COUNTIF or COUNTIFS function with an array as the criteria argument. The COUNTIF function counts the number of cells in a range that meet a single condition, while the COUNTIFS function counts the number of cells in a range that meet multiple conditions. The array criteria argument is a set of values enclosed in curly braces, such as {\"A\",\"B\",\"C\"}. This means that the function will count the cells that match any of the values in the array.

Procedures

To count values in a range of columns if they appear in an array, follow these steps:

• Select a cell where you want to enter the formula.
• Type =COUNTIF( or =COUNTIFS( depending on how many conditions you want to apply.
• Select the range of cells that you want to count, such as A2:A10.
• Type a comma (,) to separate the arguments.
• Type the array criteria argument in curly braces, such as {\"A\",\"B\",\"C\"}. You can also use cell references or named ranges to create the array, such as B2:B4 or MyArray.
• If you are using the COUNTIFS function, you can add more range and criteria arguments separated by commas, such as C2:C10,\">10\".
• Type a closing parenthesis ()) to complete the function.
• Press Enter to get the result.

Comprehensive explanation

The COUNTIF and COUNTIFS functions work by comparing each cell in the range argument with each value in the array criteria argument. If the cell matches any of the values in the array, it is counted. If the cell does not match any of the values in the array, it is ignored. The function returns the total number of cells that match the array criteria.

For example, suppose we have the following data in columns A and B:

If we want to count how many names start with A, B, or C, we can use the following formula:

=COUNTIF(A2:A10,\"{A,B,C}*\")

The asterisk (*) is a wildcard character that matches any sequence of characters. The formula counts the cells in A2:A10 that begin with A, B, or C, regardless of what follows. The result is 3, as Alice, Bob, and Charlie match the criteria.

If we want to count how many scores are between 10 and 15, we can use the following formula:

=COUNTIFS(B2:B10,\">=10\",B2:B10,\"<=15\")

The formula counts the cells in B2:B10 that are greater than or equal to 10 and less than or equal to 15. The result is 5, as Bob, David, Eve, Grace, and Harry match the criteria.

Scenario

To illustrate the use of counting values in a range of columns if they appear in an array, let’s consider a scenario where we have a list of products and their categories and prices. We want to count how many products belong to either the Electronics or the Furniture category and have a price of more than $100. The data is shown below:

To count the products that meet our criteria, we can use the following formula:

=COUNTIFS(B2:B10,\"{Electronics,Furniture}\",C2:C10,\">100\")

The formula counts the cells in B2:B10 that are either Electronics or Furniture and the cells in C2:C10 that are more than 100. The result is 4, as Laptop, Sofa, TV, and Table match the criteria.

Excel table

To make the formula easier to understand and maintain, we can use an Excel table to store the data and the criteria. To create an Excel table, select the data range and press Ctrl+T. Then, name the table and the columns in the Table Design tab. For example, we can name the table Products, the first column Product, the second column Category, and the third column Price.

Then, we can create another table to store the array criteria. For example, we can name the table Criteria, the first column Category_Criteria, and the second column Price_Criteria. We can enter the values Electronics and Furniture in the first column and the value 100 in the second column.

The tables are shown below:

To count the products that meet the criteria, we can use the following formula:

=COUNTIFS(Products[Category],Criteria[Category_Criteria],Products[Price],\">\"&Criteria[Price_Criteria])

The formula counts the cells in the Category column of the Products table that match the values in the Category_Criteria column of the Criteria table and the cells in the Price column of the Products table that are greater than the value in the Price_Criteria column of the Criteria table. The result is 4, as Laptop, Sofa, TV, and Table match the criteria.

Using Excel tables makes the formula more readable and dynamic, as we can easily change the data or the criteria without modifying the formula.

Other approaches

There are other ways to count values in a range of columns if they appear in an array, such as using the SUMPRODUCT function or the FREQUENCY function. However, these methods are more complex and require extra steps or helper columns. The COUNTIF and COUNTIFS functions are the simplest and most efficient ways to perform this task.