✦Template Printable Ideas Insights

Mastering COUNTIF: Multiple Ranges in Excel

By

The COUNTIF function in Excel is a powerful tool for counting cells that meet a specific criterion. But what if you want to count cells in multiple ranges? Here's how to do it.

Imagine you have data in various departments, and you want to count the number of projects completed in each. You can't just use COUNTIF for each range separately. Instead, you'll need to use a combination of COUNTIF and other Excel functions. Let's explore how.

Using COUNTIF with Multiple Ranges

Here's a simple way to count cells in multiple ranges using COUNTIF:

Count Blank Cells - COUNTBLANK Function
Count Blank Cells - COUNTBLANK Function
Excel COUNTIF function examples - not blank, greater than, duplicate or unique
Excel COUNTIF function examples - not blank, greater than, duplicate or unique
COUNTIFS Function
COUNTIFS Function

Let's assume you have the following ranges: A1:A10 (Range 1), B1:B15 (Range 2), and C1:C20 (Range 3). You want to count the number of cells with "Completed" in each range.

COUNTIF for Single Range

To count "Completed" in Range 1 (A1:A10), you would use: ``` =COUNTIF(A1:A10, "Completed") ```

Count Blank Cells - COUNTBLANK Function
Count Blank Cells - COUNTBLANK Function
the temperature chart for an airplane is shown in this graphic style, with different colors and sizes
the temperature chart for an airplane is shown in this graphic style, with different colors and sizes
a kitchen with white cabinets and stainless steel appliances in the center is circled by a red circle
a kitchen with white cabinets and stainless steel appliances in the center is circled by a red circle

This function counts the number of cells in range A1:A10 where the value is "Completed".

Combining COUNTIF for Multiple Ranges

Now, let's extend this to multiple ranges. You can use the SUM function to add the results of each COUNTIF together. Here's how: ``` =SUM(COUNTIF(A1:A10, "Completed"), COUNTIF(B1:B15, "Completed"), COUNTIF(C1:C20, "Completed")) ```

This formula will give you the total number of cells in all three ranges that contain the text "Completed".

a white kitchen with stainless steel appliances and marble counter tops, along with an island in the middle
a white kitchen with stainless steel appliances and marble counter tops, along with an island in the middle
a stove top oven sitting inside of a kitchen next to a counter with food on it
a stove top oven sitting inside of a kitchen next to a counter with food on it
Extract unique values based on one or more criteria in Excel
Extract unique values based on one or more criteria in Excel
I Toured Lots of Bad Flips — Here Are the 5 Green Flags Our New Home Was Well-Constructed
I Toured Lots of Bad Flips — Here Are the 5 Green Flags Our New Home Was Well-Constructed
IF function in Excel formulas for numbers, text, dates step by step
IF function in Excel formulas for numbers, text, dates step by step
21 Double Oven Kitchen Layout
21 Double Oven Kitchen Layout

COUNTIFS: A Shorter Way for Multiple Conditions

Introduced in Excel 2007, COUNTIFS is a more efficient way to count cells that meet multiple criteria, across multiple ranges.

COUNTIFS for Multiple Ranges and One Criterion

To count "Completed" in all three ranges using COUNTIFS, you would use: ``` =COUNTIFS(A1:A10, "Completed", B1:B15, "Completed", C1:C20, "Completed") ```

This function counts the number of cells where the criterion "Completed" is met in all three ranges.

Double Oven Kitchen Layout Planning Guide
Double Oven Kitchen Layout Planning Guide
a kitchen with white cabinets and stainless steel stove top ovens, wood flooring
a kitchen with white cabinets and stainless steel stove top ovens, wood flooring
a stainless steel stove and oven in a kitchen with tile backsplashing on the wall
a stainless steel stove and oven in a kitchen with tile backsplashing on the wall

COUNTIFS for Multiple Ranges and Multiple Criteria

You can also use COUNTIFS to count cells that meet different criteria in different ranges. For example, to count "Completed" in Range 1 and "In Progress" in Ranges 2 and 3, you would use: ``` =COUNTIFS(A1:A10, "Completed", B1:B15, "In Progress", C1:C20, "In Progress") ``` This function counts the number of cells that meet the specified criteria in each range.

And there you have it! You now know how to use COUNTIF with multiple ranges in Excel. This should make your data analysis tasks a lot easier. Next time you're faced with counting cells across multiple ranges, give these methods a try!