How to count the occurrences of few items in a list of items in Google Sheets. It’s easy with Countif. Use Countif to count a column with multiple conditions in Google Doc Sheets.
As a side note, if you are looking for different options to perform count in Google Sheets like count, logical count (Countif, Countifs etc.) and database count (Dcount) check my Count function tutorial.
Back to count a list with multiple criteria in Google Sheets. I am using a simple Countif Array formula for this. Though the formula may return individual counts of each item we can sum that using the basic math function Sum or Sumproduct.
If you use Sumproduct together with Countif, then you can skip the ArrayFormula. See that example to understand what I am talking about.
Example: List of Items in Column B and the Conditions to Count in Column E.
Now see the Countif formula that you can use to count column B with the multiple conditions in column E in Google Sheets.
Countif to Count a Column With Multiple Conditions in Google Sheets
I just want the count the availability of the multiple conditions in the range E3: E5 in column B list.
As famously known, to perform a conditional count across a range, the best and fast performing function is Countif.
Syntax:
COUNTIF(range, criterion)
The Countif formula below wouldn’t serve our purpose since I have used criteria, not a criterion. That means there are multiple conditions to count.
=countif(B2:B16,E3:E5)
So what to do?
Make the formula work correctly with multiple conditions or you can say criteria in an array or range. How? Wrap the Countif with the ArrayFormula which transforms a non-array formula into an array formula.
=ArrayFormula(countif(B2:B16,E3:E5))
This is enough to count a column with multiple conditions in Google Sheets. Because it returns the count of each condition separately. But additionally, use the Sum as below to get the total count.
=sum(ArrayFormula(countif(B2:B16,E3:E5)))
Result: 6
You can shorten this formula using the Sumproduct function. Instead of using ArrayFormula and Sum, you just need to use Sumproduct with Countif as below.
=sumproduct(countif(B2:B16,E3:E5))
Result: 6
You can use Countif+Sumproduct combo as above when you want to count a list with many conditions in Google Sheets. The Sumproduct itself is an ArrayFormula. So there is no requirement of the additional use of ArrayFormula.
Related Reading:
1. COUNTIF to Count by Month in a Date Range in Google Sheets.
2. Countif in an Array in Google Sheets Using Vlookup and Query Combo.
3. How to Use COUNTIF with UNIQUE in Google Sheets.
4. How to Perform a Case Sensitive COUNTIF in Google Sheets.
