Countifs with Multiple Criteria in Same Range in Google Sheets

0
146
Countifs with Multiple Criteria in Same Range in Google Sheets

Unlike Excel, it’s not easy or nearly impossible to use Countifs with multiple criteria in same range in Google Sheets. There are alternative functions like Query which is far better in this case. But many of the users who are migrated from Excel to Google Sheets are not familiar in using Query. So I dedicate this tutorial to such users.

In Google Sheets you can intelligently replace Excel’s Countifs formula with a Countif formula. So we can use Countif in Google Sheets as an alternative to Countifs but with a workaround.

In order to use multiple criteria in same column range in Countifs, we should make use of the curly braces as below. It’s applicable to Excel only.

{“Apple”,”Orange”,”Pomegranates”}

You can scroll down to see the Formula 1 where I’ve used the Curly Braces as OR condition in same column, that means multiple criteria in same column range. But there is a big difference in the usage of Curly Braces in Excel and Google Sheets.

Of course, in Google Sheets also we can use the Curly Braces to generate Arrays. But not the same way as in Excel. In Google Sheets, you can make use of Curly Braces in Countif not in Countifs. But in Google Sheets, additionally we should use ArrayFormula with Curly Braces and that makes a lot of difference.

Similar: Learn All Count Functions in Google Doc Spreadsheet

See the examples below.

Countifs with Multiple Criteria in Same Range in Excel

I am making use of the below two column sample data set for our example purpose.

Examples to Multiple Countifs Criteria in Same Range

I just want to count the number of occurrences of different fruits items on a particular date. Here I am using today’s date as that particular date and as per my system today’s date is 31/01/2018. I only want to count the items “Apple”, “Orange” and “Pomegranates” that appear in this date.

First let’s see the use of COUNTIFS formula in Excel. Then we can see how to use Countifs with Multiple Criteria in Same Range in Google Sheets.

Formula 1:

Excel Formula
=SUM(COUNTIFS(A:A,TODAY(),B:B,{“Apple”,”Orange”,”Pomegranates”}))
Note: Copied formulas from this page may not work unless you re type double quotes.

In Excel it’s quite easy. Excel accepts the OR condition inside curly braces as above. This formula counts all the items named “Apple”, “Orange” and “Pomgranges” in Column B if the corresponding date in column A is 31/01/2018.

Countifs with Multiple Criteria in Same Range in Google Sheets

Now Let’s learn how to use Countifs, you may read Countif, with multiple criteria in same range in Google Sheets.

In Google Sheets you can’t use the formula as above. Multiple criteria in Countifs in Same Column is not possible or recommended in Google Sheets. So the nearest possible workaround is the use of Countif. See below the Google Sheets equivalent to Excel’s Countifs formula above.

Formula 2:

Google Sheets Formula
=ArrayFormula(sum(countif(if(A1:A=today(),B1:B),{“Apple”,”Orange”,”Pomegranates”})))

Formula Explanation:

The use of IF function in this formula is to return all the values in column B if the corresponding values in Column A are today’s date. I’ve extracted the IF part from the above formula for your reference and applied in D4. See that below.

Explanation Formula 1:

=ArrayFormula(if(A1:A10=today(),B1:B10))

Result:the use of arrayformula and if functions in countif

Wherever the date is not matching, the formula returns FALSE. Now I am using a Countif formula keeping the above D4:D13 as the range.

Explanation Formula 2:

=ArrayFormula(sum(countif(D4:D13,{“Apple”,“Orange”,“Pomegranates”})))

Unlike Countifs, in Google Sheets you can use multiple criteria in same column using Countif but in a single column as above.

This formula counts all the values in the range D4:D13 for items “Apple”, “Orange”, and “Pomegranates”

This’s what I have done in our main formula (formula 2). There I’ve just replaced the range D4:D13 with the Explanation Formula 1 above. Just see the blue colour patterns in the formulas above.

Conclusion

With this tutorial, I hope, I could shed some light in to the above and the following similar user queries.

  1. Countif OR – How to use OR in Countif function in Google Sheets similar to Excel?
  2. Same field twice or more times in Countifs, how to?
  3. The sue of Curly Brackets in Countif
  4. Why Countifs is not accepting Curly Braces in Google Sheets?

That’s all. See you again with another advanced Google Sheets tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here