Countif With Non

Explanation

In this example, the goal is to count values in three non-contiguous ranges with criteria. To be included in the count, values must be greater than 50. The COUNTIF counts the number of cells in a range that meet the given criteria. However, COUNTIF does not perform counts across different ranges. If you try to use COUNTIF with multiple ranges separated by commas, or in an array constant , you’ll get an error. There are several ways to approach this problem, as explained below.

INDIRECT and COUNTIF

The INDIRECT function converts a given text string into a proper Excel reference:

=INDIRECT("A1") // returns A1

One approach is to provide the ranges as text in an array constant to INDIRECT like this:

INDIRECT({"B5:B8","D7:D10","F6:F11"})

Then pass the result from INDIRECT into the COUNTIF function like this:

=COUNTIF(INDIRECT({"B5:B8","D7:D10","F6:F11"}),">50")

INDIRECT will evaluate the text values and pass the references into COUNTIF as the range argument. For reasons mysterious, COUNTIF will accept the result from INDIRECT without complaint. Because COUNTIF receives more than one range, it will return more than one result in an array like this:

={4,2,3}

The three numbers in this array are the counts of numbers greater than 50 in each of the three ranges. To “catch” these results and return a total, we use the SUM function:

=SUM({4,2,3}) // returns 9

The SUM function then returns the sum of all values, 9. Although this is an array formula, it does not require CSE , since we are using an array constant.

Note: INDIRECT is a volatile function and can impact workbook performance.

More than one COUNTIF

Another way to solve this problem is to use more than one COUNTIF:

=COUNTIF(B5:B8,">50")+COUNTIF(D7:D10,">50")+COUNTIF(F6:F11,">50")

With a limited number of ranges, this approach may be easier to implement. It avoids possible performance impacts of INDIRECT and allows a normal formula syntax for ranges, so ranges will update automatically with worksheet changes. The INDIRECT example above relies on text strings that need to be updated manually.

VSTACK function

In current versions of Excel, a better approach is to first combine the ranges, and then perform the conditional count. To combine all three ranges vertically, you can use the VSTACK function :

=VSTACK(B5:B8,D7:D10,F6:F11)

The result from VSTACK is a single array with 14 values. Unfortunately, we can’t pass this result into the COUNTIF function, because COUNTIF is in a group of functions that require actual ranges . However, we can use the SUM function and Boolean algebra to perform the conditional count. The complete formula looks like this:

=SUM(--(VSTACK(B5:B8,D7:D10,F6:F11)>50))

After VSTACK runs, and all values are checked with >50, we have an array of 14 TRUE and FALSE values like this:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}

The double negative (–) is used to convert the TRUE and FALSE values to 1s and 0s, and the resulting array is returned directly to the SUM function :

=SUM({1;1;1;1;1;1;0;0;1;0;1;1;0;0}) // returns 9

The SUM function sums the array and returns 9 as a final result.

Video: Boolean operations in array formulas

Explanation

In this example, the goal is to use the COUNTIFS function to count data with “OR logic”. The challenge is the COUNTIFS function applies AND logic by default.

COUNTIFS function

The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range:

=COUNTIFS(range1,criteria1)

Count cells in range1 that meet criteria1.

By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, ALL conditions must match in order to generate a count:

=COUNTIFS(range1,criteria1,range1,criteria2)

Count where range1 meets criteria1 AND range1 meets criteria2.

This means if we try to user COUNTIFS like this:

=COUNTIFS(D5:D16,"complete",D5:D16,"pending") // returns 0

The result is zero, since the order status in column D can’t be both “complete” and “pending” at the same time. One solution is to supply multiple criteria in an array constant like this:

=COUNTIFS(D5:D16,{"complete","pending"})

This will cause COUNTIFS to return two results: a count for “complete” and a count for “pending” in array like this:

{6,3}

In the current version of Excel , these results will spill onto the worksheet into two cells. To get a final total in one formula, we nest the COUNTIFS formula inside the SUM function like this:

=SUM(COUNTIFS(D5:D16,{"complete","pending"}))

COUNTIFS returns the counts directly to SUM:

=SUM({6,3}) // returns 9

And the SUM function returns the sum of the array as a final result.

Note: this is an array formula but it doesn’t require special handling in Legacy Excel when using an array constant as above. If you use a cell reference for criteria instead of an array constant, you will need to enter the formula with Control + Shift + Enter in Legacy Excel.

Adding another OR criteria

You can add one additional criteria to this formula, but you’ll need to use a single column array for criteria1 and a single row array for criteria2. So, for example, to count orders that are “Complete” or “Pending”, for “Andy Garcia” or “Bob Jones”, you can use:

=SUM(COUNTIFS(D4:D16,{"complete","pending"},C4:C16,{"Bob Jones";"Andy Garcia"}))

Note we use a comma in the first array constant for a horizontal array and a semicolon for the second array constant for a vertical array. With this configuration, Excel “pairs” elements in the two array constants, and returns counts in a two dimensional array like this

The array result from COUNTIFS is returned directly to the SUM function:

=SUM({1,1;1,0}) // returns 3

And SUM returns the final result, which is 3 in this case.

Note: this technique will only handle two range/criteria pairs. If you have more than two criteria, consider a SUMPRODUCT formula as described here .

Cell reference for criteria

As mentioned above, you can use a cell reference for criteria in an array formula like this:

={SUM(COUNTIFS(range,B1:B2))}

Where range is the criteria range, and B1:B2 is an example cell reference that contains criteria. This formula “just works” in the current version of Excel, which supports dynamic array formulas . However, in Legacy Excel , the formula must be entered with Control + Shift + Enter.

Wildcards and double-counting

COUNTIF and COUNTIFS support wildcards , but you need to be careful not to double-count when you have multiple “contains” conditions with OR logic. See this example for more information