Using the Excel COUNTIFS Function with Multiple Criteria and the OR Logic

The sample dataset contains information about Products, their Payment Status and Quantity.

To count the number of laptops and keyboards with pending or paid status:

countifs with multiple criteria and or logic

Example 1 – Using the SUM and COUNTIFS Functions with Multiple Criteria and the OR Logic

Use Excel SUM and COUNTIFS Functions with Multiple Criteria and OR Logic

STEPS:

Select F13 and enter the formula below:

=SUM(COUNTIFS(B5:B13,{"Laptop","Keyboard"},D5:D13,{"Paid";"Pending"}))

Press Enter to see the result.

Use Excel SUM and COUNTIFS Functions with Multiple Criteria and OR Logic

The criteria range and criteria were entered in the COUNTIFS function. The first criteria looks for a Laptop or Keyboard in B5:B13 and the second criteria looks for Paid or Pending in D5:D13.

Formula Breakdown

COUNTIFS(B5:B13,{“Laptop”,”Keyboard”},D5:D13,{“Paid”;”Pending”}): This output is an array: {2,0,1,1}. It can be displayed as shown below:

The number of Paid Laptops is 2 and Pending is 1. The number of Paid Keyboards is 0 and Pending is 1. The total number is 4.

SUM(COUNTIFS(B5:B13,{“Laptop”,”Keyboard”},D5:D13,{“Paid”;”Pending”})): The SUM function adds the array {2,0,1,1} and displays the result: 4.

Example 2 – Applying the Excel COUNTIFS Function with the Plus Operator to Count Cells with Multiple Criteria

The dataset is slightly different.

To count the number of cells that contain Laptop and Keyboard with Paid or Pending status:

STEPS:

Select F13 and enter the formula below:

=COUNTIFS(B5:B13,$F$6,D5:D13,$F$9)+COUNTIFS(B5:B13,$F$6,D5:D13,$F$10)+COUNTIFS(B5:B13,$F$7,D5:D13,$F$9)+COUNTIFS(B5:B13,$F$7,D5:D13,$F$10)

Press Enter to see the result.

Excel COUNTIFS Function with Plus Operator to Count Cells with Multiple Criteria

An absolute cell reference was used.

Formula Breakdown

The formula is broken into four parts: each part counts the cell number specified by the condition.

COUNTIFS(B5:B13,$F$6,D5:D13,$F$9): counts the number of cells that contain Keyboard in B5:B13 and Pending in D5:D13.
COUNTIFS(B5:B13,$F$6,D5:D13,$F$10): counts the number of cells that contain Keyboard in B5:B13 and Paid in D5:D13.
COUNTIFS(B5:B13,$F$7,D5:D13,$F$9): counts the number of cells that contain Laptop in B5:B13 and Pending in D5:D13.
COUNTIFS(B5:B13,$F$7,D5:D13,$F$10): counts the cell numbers that store Laptop in B5:B13 and Paid in D5:D13.
The Plus (+) operator sums the value and shows the result.

Example 3 – Counting Cells Using the Excel COUNTIFS Formula with the OR and the AND Logic

Count the number of cells that contain Laptop or Keyboard in B5:B13 and Paid in D5:D13:

STEPS:

Select F12 and enter the formula below:

=COUNTIFS(B5:B13,F6:F7,D5:D13,F10)

Press Ctrl + Shift + Enter to see the result.

Count Cells Using Excel COUNTIFS Formula with OR as well as AND Logic

This formula is an array formula. {0,2} is the result.

The first condition looks for Keyboard or Laptop in B5:B13.
The second condition looks for Paid in D5:D13.

To sum the array {0,2}, use the formula below:

=SUM(COUNTIFS(B5:B13,F6:F7,D5:D13,F10))

Alternative to the Excel COUNTIFS Function to Count Cells with Multiple Sets of OR Criteria

Consider the dataset in Example 1. To count cells that contain Laptop or Keyboard with the payment status Paid or Pending:

STEPS:

Select F13 and enter the formula below:

=SUMPRODUCT(ISNUMBER(MATCH(B5:B13,{"Laptop","Keyboard"},0))*ISNUMBER(MATCH(D5:D13,{"Paid","Pending"},0)))

Press Enter to see the result.

Alternative to Excel COUNTIFS Function to Count Cells with Multiple Sets of OR Criteria

The MATCH function looks for the exact match in the selected range. The ISNUMBER function checks if the returned value is a number. If it is a number, it returns TRUE. Otherwise, FALSE.

Formula Breakdown