Method 1 – Use Multiple COUNTIF Function in Multiple Ranges for Same Criteria
Steps:
- Select a cell with certain criteria and input the following formula.
=COUNTIF(D5:D9,">50")+COUNTIF(D13:D17,">50")We applied the COUNTIF function to count the number of contributions in terms of goals or assists more than 50 in ranges D5:D9 and D13:D17.
- Press ENTER to have the output.
Method 2 – Insert Multiple COUNTIF in Multiple Ranges for Specific Criteria
Steps:
- Pick a cell with a defined criteria and input the following formula to count the number of players with zero contributions.
=COUNTIF(D5:D9,0) + COUNTIF(D13:D17,0)
- Hit the ENTER button to have the defined result.
Method 3 – Combine COUNTIF, SUMPRODUCT, & INDIRECT Functions in Multiple Ranges for Same Criteria
Steps:
- Choose a cell first.
- Apply the following formula to count from multiple ranges with same criteria.
=SUMPRODUCT(COUNTIF(INDIRECT({"C5:C9","C13:C17"}),"Germany"))Count the cells holding Germany name from cells C5:C9 and C13:C17.
- Press ENTER.
Method 4 – Use COUNTIFS in Multiple Ranges for Same Criteria
Steps:
- Input the following formula with the COUNTIFS Function to count the values from multiple ranges.
=COUNTIFS(C5:C9 : C13:C17,"=Argentina")
- You will have the output by pressing the ENTER button.
Method 5 – Application of COUNTIF for Same Date in Excel
Steps:
- Input the following formula in a selected cell to count the cells with same criteria in multiple ranges.
=COUNTIF(C5:C9,"<1/1/1995")+COUNTIF(E5:E9,"<1/1/1995")We defined the formula to count the cells less than 1/1/1995 from the ranges C5:C9 and E5:E9.
- Press the ENTER button to have the output.
Download Practice Workbook
For more expertise, you can practice here.
Related Articles
- Apply SUM and COUNTIF for Multiple Criteria in Excel
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- How to Use COUNTIF Function with Array Criteria in Excel
- COUNTIF Between Two Values with Multiple Criteria in Excel
- Excel COUNTIF to Count Cells Greater Than 1
- How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
The part under “4. Using COUNTIFS Function” is incorrect and misleading.
Specifying the input range as C4:C10:E4:E10 is exactly the same specification as C4:E10. Meaning in this way of writing you are referring to a single range and not to “Multiple Ranges Same Criteria in Excel” as the title is promising. You better remove this section.
Related to comment above and my experience. Is it possible to select two different ranges not near each other?
As when I attempt this it shows the two different ranges highlighted however the formulas returns other ranges in between.
Example:
=sum(countifs(b4:h4:b10:h10, {“A”}))