✦Template Printable Ideas Insights

Mastering Multiple Criteria: CountIf in Google Sheets

By

Ever needed to count cells in Google Sheets based on multiple criteria? The built-in COUNTIF function can handle this, but it requires a bit of finessing. Let's dive in.

First, understand that COUNTIF alone only checks for a single condition. To count based on multiple criteria, we'll need to use multiple COUNTIF functions, often with the help of other functions like SUM or IF.

Using COUNTIF with OR criteria

The COUNTIF function supports 'OR' logic using the asone or SOME evaluators (available in new versions). Here's the syntax:

The Ultimate Google Sheets Formulas Cheat Sheet
The Ultimate Google Sheets Formulas Cheat Sheet
COUNT functions in google sheet or excel
COUNT functions in google sheet or excel
How to Use COUNTIF on Google Sheets, Infographic
How to Use COUNTIF on Google Sheets, Infographic

COUNTIF(range, {"Condition1", "Condition2", ...})

Example 1: Count based on multiple categories

Suppose you have a 'Sales' sheet with columns 'Category' and 'Total'. You want to find the total sales for both 'Women's Clothing' and 'Accessories'.

Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function
the spreadsheet is open and ready to be used for project management, as well as other tasks
the spreadsheet is open and ready to be used for project management, as well as other tasks

Use this formula: =COUNTIFS(Sales!A:A, {"Women's Clothing", "Accessories"}, Sales!B:B, ">0")

Example 2: Count non-blank cells in multiple columns

In a 'Data' sheet, you want to count the number of rows with data in columns A and B.

Formula: =COUNTIFS(Data!A:A, "<>"&"", Data!B:B, "<>"&"")

the google sheets are in green and white
the google sheets are in green and white
Celebrate #PRIDE month in Google Sheets
Celebrate #PRIDE month in Google Sheets
How can I highlight duplicate values in Google Sheets? 🤔💡
How can I highlight duplicate values in Google Sheets? 🤔💡
Google Sheets - CountIF, CountIFS Functions Intro Tutorial in Tamil
Google Sheets - CountIF, CountIFS Functions Intro Tutorial in Tamil
How to Create a Date Countdown in Google Sheets, Infographic
How to Create a Date Countdown in Google Sheets, Infographic
Google Sheets gets macros | TechCrunch
Google Sheets gets macros | TechCrunch

Using SUM with IF and COUNTIF

When counting cells that meet multiple criteria and criteria aren't easily 'OR'd together, we'll use a combination of IF and COUNTIF.

Example 3: Count sales based on category and regionalությամբ target

In a 'Large Dataset' sheet, find the total sales for 'Electronics' where the 'Regional Target' is 'West' or 'East'.

Formula: =SUM(IFS(Large Dataset!B:B={"Electronics"}, IF(Large Dataset!C:C={"West","East"}, Large Dataset!D:D, 0))

How To Use COUNTIF In Google Sheets | Formula Quick Guide
How To Use COUNTIF In Google Sheets | Formula Quick Guide
6 Ways to Use Google Sheets for Data Analysis
6 Ways to Use Google Sheets for Data Analysis
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)

Example 4: Count instances meeting complex criteria

Count the number of products where 'Stock' is 'In' and 'Sales' is 'High' (assuming 'High' is defined as sales greater than 1000).

Formula: =SUM(IFS(Large Dataset!C:C={"In"}, Large Dataset!D:D>1000))

Mastering COUNTIF with multiple criteria requires practice and familiarity with function parameters. Keep experimenting, and you'll be a pro in no time.

Remember, the key is to break down complex conditions into manageable parts, then bring them back together with functions like SUM or IF. Now go forth and harness the power of counting with multiple criteria!