How to Use COUNTIF with Multiple Ranges in Excel

Using COUNTIF with multiple ranges in Excel allows you to count the number of cells that meet certain criteria across various ranges. To do this, you will need to use the SUM function along with multiple COUNTIF functions for each range.

Here's a step-by-step guide on how to use COUNTIF with multiple ranges in Excel:

1. Open your Excel workbook and identify the multiple ranges you want to apply the COUNTIF function to.
2. Click on an empty cell where you want to display the result.
3. Type the following formula, replacing "Range1", "Range2", "Range3", and "Criteria" with the appropriate values for your data:

=SUM(COUNTIF(Range1, Criteria), COUNTIF(Range2, Criteria), COUNTIF(Range3, Criteria))

4. Press Enter to obtain the result.

Example

Let's say we have the following data in an Excel spreadsheet:

A      B      C
1      5      9
2      6      7
3      5      1

We want to count the number of cells containing the value "5" across all three columns (ranges A1:A3, B1:B3, and C1:C3).

1. Click on an empty cell, like D1, to display the result.
2. Type the following formula:

=SUM(COUNTIF(A1:A3, 5), COUNTIF(B1:B3, 5), COUNTIF(C1:C3, 5))

3. Press Enter.

The result in cell D1 would be "3", as there are three cells containing the value "5" across the specified ranges.