» Counting Based on Multiple Criteria

In this example, we will count the number of rows in which the text ExcelTip and USA appear in both columns B and C (the range Name for column B is Customer_Name, and for column C it is Market).

To count the number of rows that must satisfy criteria from two columns:

Insert the following Array Formula (see how to insert an Array Formula in page 2 of this Tip):
{=SUM((Market="USA")*(Customer_Name="ExcelTip"))}

The result of the calculation is 2. The * symbol in the Array Formula returns a result equal to the AND operator.

To count the number of cells that satisfy at least one of two criteria from different ranges:

Replace the * symbol in the Array Formula with a + sign.
The return result calculation is 5, that is, five cells in column B match the criteria ExcelTip and four cells in column C match the criteria USA.
Note: This can also be done by replacing the Array Formula with a COUNTIF formula:
=COUNTIF (Market, "USA")+ COUNTIF(Customer_Name, "ExcelTip")

Using keyboard shortcuts

The keyboard shortcut for inserting an Array Formula is as follows (see Tip #201):

After entering the formula into the cell, select the cell, press F2 to edit it, and then press Ctrl+Shift+Enter.
Screenshot // Counting Based on Multiple Criteria