Counting Cells with Different Conditional Ranges in Excel Formula

Sometimes, you may want to count the number of cells that meet certain criteria in different ranges. For example, you may want to count how many cells contain “Apple” in one range and “John” in another range. Or, you may want to count how many cells have values between 10 and 20 in one range and between 30 and 40 in another range.

There are different ways to achieve this task in Excel, depending on the complexity and size of your data. In this article, we will introduce some of the most common methods, such as using COUNTIF, COUNTIFS, SUMPRODUCT, and MMULT functions. We will also explain the basic theory behind each method, and provide a detailed example with real numbers and an Excel table.

Before we dive into the specific methods, let’s review some basic concepts that are useful for understanding how to count cells with different conditional ranges in Excel formula.

Criteria

A criterion is a condition that defines which cells to count. A criterion can be a number, a text, a cell reference, an expression, or a function. For example, “>10” is a criterion that counts cells that are greater than 10. “Apple” is a criterion that counts cells that contain the word “Apple”. A1 is a criterion that counts cells that are equal to the value in cell A1. “<>0” is a criterion that counts cells that are not zero. COUNTIF(A1:A10,“sweets”) is a criterion that counts cells that are equal to the result of the COUNTIF function.

Range

A range is a group of contiguous or non-contiguous cells that you want to apply the criteria to. A range can be a single cell, a row, a column, or a block of cells. For example, A1:A10 is a range that consists of 10 cells in column A. B5:B8,D7:D10,F6:F11 is a range that consists of three non-contiguous blocks of cells.

Logical Operators

Logical operators are symbols that combine or compare criteria. There are two main types of logical operators: AND and OR. The AND operator returns TRUE only if all the criteria are TRUE. The OR operator returns TRUE if any of the criteria is TRUE. For example, A1>10 AND A1<20 is a criterion that returns TRUE only if the value in cell A1 is between 10 and 20. A1=“Apple” OR A1=“Orange” is a criterion that returns TRUE if the value in cell A1 is either “Apple” or “Orange”.

Methods

Now that we have covered the basic theory, let’s see how to use different functions to count cells with different conditional ranges in Excel formula.

COUNTIF

The COUNTIF function counts the number of cells that meet a single criterion in a single range. The syntax of the COUNTIF function is:

=COUNTIF(range, criterion)

For example, =COUNTIF(A1:A10,“>10”) counts the number of cells in range A1:A10 that are greater than 10.

To count cells with different conditional ranges using the COUNTIF function, you can use the INDIRECT and SUM functions together. The INDIRECT function converts a text string into a cell reference. The SUM function adds up the results of the COUNTIF functions. The syntax is:

=SUM(COUNTIF(INDIRECT({“range1”,“range2”,…}),“criterion”))

For example, =SUM(COUNTIF(INDIRECT({“B5:B8”,“D7:D10”,“F6:F11”}),“>50”)) counts the number of cells in ranges B5:B8, D7:D10, and F6:F11 that are greater than 50.

The advantage of this method is that it is simple and easy to use. The disadvantage is that it only works for a single criterion and a single range. If you have multiple criteria or multiple ranges, you need to use other methods.

COUNTIFS

The COUNTIFS function counts the number of cells that meet multiple criteria in multiple ranges. The syntax of the COUNTIFS function is:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], …)

For example, =COUNTIFS(A1:A10,“>10”,B1:B10,“<20”) counts the number of cells in range A1:A10 that are greater than 10 and in range B1:B10 that are less than 20.

To count cells with different conditional ranges using the COUNTIFS function, you can use the same range for multiple criteria, or use different ranges for different criteria. The syntax is:

=COUNTIFS(range1, criterion1, range2, criterion2, …)

For example, =COUNTIFS(A1:C3,“Apple”,D1:D3,“John”) counts the number of cells in range A1:C3 that contain “Apple” and in range D1:D3 that contain “John”.

The advantage of this method is that it can handle multiple criteria and multiple ranges. The disadvantage is that it only works for AND logic, meaning that all the criteria must be met. If you want to use OR logic, meaning that any of the criteria can be met, you need to use other methods.

SUMPRODUCT

The SUMPRODUCT function multiplies corresponding elements in one or more arrays and returns the sum of the products. The syntax of the SUMPRODUCT function is:

=SUMPRODUCT(array1, [array2], …)

For example, =SUMPRODUCT(A1:A10,B1:B10) returns the sum of the products of the values in range A1:A10 and B1:B10.

To count cells with different conditional ranges using the SUMPRODUCT function, you can use logical expressions to create arrays of TRUE and FALSE values, and then convert them to 1 and 0 by using the double negative sign (–). The syntax is:

=SUMPRODUCT(–(range1=criterion1),–(range2=criterion2),…)

For example, =SUMPRODUCT(–(A1:C3=“Apple”),–(D1:D3=“John”)) counts the number of cells in range A1:C3 that contain “Apple” and in range D1:D3 that contain “John”.

The advantage of this method is that it can handle both AND and OR logic, depending on how you combine the arrays. The disadvantage is that it can be complex and slow for large data sets.

MMULT

The MMULT function returns the matrix product of two arrays. The syntax of the MMULT function is:

=MMULT(array1, array2)

For example, =MMULT(A1:B2,C1:C2) returns the matrix product of the values in range A1:B2 and C1:C2.

To count cells with different conditional ranges using the MMULT function, you can use logical expressions to create arrays of TRUE and FALSE values, and then use the TRANSPOSE and COLUMN functions to create a matrix of 1 and 0 values. The syntax is:

=SUMPRODUCT(–(MMULT(–(range1=criterion1),TRANSPOSE(COLUMN(range1)^0))>0),–(range2=criterion2))

For example, =SUMPRODUCT(–(MMULT(–(A1:C3=“Apple”),TRANSPOSE(COLUMN(A1:C3)^0))>0),–(D1:D3=“John”)) counts the number of cells in range A1:C3 that contain “Apple” and in range D1:D3 that contain “John”.

The advantage of this method is that it can handle multiple occurrences of the criterion in the same row, and only count each row once. The disadvantage is that it is very complicated and hard to understand.

Example

To illustrate how to use the different methods, let’s look at an example with real numbers and an Excel table. Suppose we have the following data in Excel:

We want to count how many cells in range A1:C3 have values between 10 and 20, and how many cells in range D1:F3 contain “John” or have values between 25 and 30.

Using the COUNTIF function, we can enter the following formula:

=SUM(COUNTIF(INDIRECT({“A1:C3”,“D1:F3”}),“>=10”))-SUM(COUNTIF(INDIRECT({“A1:C3”,“D1:F3”}),“>20”))+SUM(COUNTIF(INDIRECT({“D1:F3”}),“John”))

The result is 11.

Using the COUNTIFS function, we can enter the following formula:

=COUNTIFS(A1:C3,“>=10”,A1:C3,“<=20”)+COUNTIFS(D1:D3,“John”)+COUNTIFS(E1:F3,“>=25”,E1:F3,“<=30”)

The result is 11.

Using the SUMPRODUCT function, we can enter the following formula:

=SUMPRODUCT(–(A1:C3>=10),–(A1:C3<=20))+SUMPRODUCT(–(D1:D3=“John”))+SUMPRODUCT(–(E1:F3>=25),–(E1:F3<=30))

The result is 11.

Using the MMULT function, we can enter the following formula:

=SUMPRODUCT(–(MMULT(–(A1:C3>=10),TRANSPOSE(COL(A1:C3)^0))>0),–(D1:D3=“John”)±-(E1:F3>=25)±-(E1:F3<=30))

The result is 11.

In this article, we have learned how to count cells with different conditional ranges in Excel formula. We have introduced four different methods: COUNTIF, COUNTIFS, SUMPRODUCT, and MMULT. We have also explained the basic theory behind each method, and provided a detailed example with real numbers and an Excel table.