How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Friday

Varying Array Sizes in Countifs in Google Sheets

Posted by   on

If you use varying array sizes in a Countifs formula in Google Sheets, you will get an error. It’s because the function doesn’t support such usage.


It usually happens when you specify mismatching columns in criteria range 1 and 2 in Countifs.


You may please go through the function syntax first. It’s as follows.


Syntax: COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])


When you specify multiple criteria ranges, all the arrays/ranges should have the same size vertically and horizontally.


That means the number of rows and columns should match in all the specified criteria ranges/arrays.


Let’s quickly go to an example to understand it.


=countifs(A2:D10,"Apple",E2:E10,"Yes")

Varying Array Sizes in Countifs and Error in Google Sheets

Here are the four arguments in use.


1. criteria_range1 – A2:D10


2. criterion1 – “Apple”


3. criteria_range2 – E2:E10


4. criterion2 – “Yes”


As you can see, the above two array arguments (criteria_range1 and criteria_range2) to COUNTIFS are different in size.


There are four columns in the first array (argument 1) and one column in the second array (argument 3).


In the above example, I want to count the “Apple” in multiple column range A2:D10 if the values in E2:E10=”Yes.”


Workarounds to Use Varying Array Sizes in Countifs in Google Sheets


I have three workaround formulas for you to try when you have varying array sizes in Countifs that cause the #VALUE! as above.


I may be able to come up with more. But the below options will be sufficient for you.


Option 1 – Varying Array Sizes in Countifs with Virtual Criteria Ranges


We can follow different approaches to use varying array sizes in Countifs in Google Sheets.


To logically fit the Countifs rule, we can create a virtual criteria_range2 with an equal number of columns.


We have earlier adopted this method in SUMIF, i.e., to return the Sum of Matrix Rows or Columns Using Sumif in Google Sheets.


I am following the same technique with COUNTIFS here.


Formula # 1:


=countifs(A2:D10,"Apple", ARRAYFORMULA(if(E2:E10="Yes",column(A2:D2)^0)),1)

Result: 9


The above is the first option to specify mismatching columns in criteria range 1 and 2 in Countifs.


Want to learn the above COUNTIFS formula in detail?


Formula Explanation


criteria_range1 – A2:D10


criterion1 – “Apple”


criteria_range2 – ARRAYFORMULA(if(E2:E10="Yes",column(A2:D2)^0))


criterion2 – 1


In the Countifs (Formula # 1), the criteria_range2 is an array formula.


It would return an array with the number of columns equal to the criteria_range1.


The number of rows is already equal in both arrays, i.e., in criteria_range1 and 2.


This virtual array will have the number 1 in each column corresponding to “Yes” in E2:E10. That’s why I have used # 1 instead of “Yes” in criterion_2.


Varying Array Sizes in Countifs - Solution

Here are the other two options that you can consider over the above formula.


Option 2 – Based on Filtering and Logical Tests


Without virtually matching varying array sizes in Countifs, we can get our desired result.


You can find below two such formula combinations, and I have marked my recommended formula also.


Formula # 2 (Recommended):


=countif(flatten(filter(A2:D10,E2:E10="Yes")),"Apple")

Here I have used Countif instead of Countifs as we have only one column after manipulating the data with FILTER and FLATTEN.


It works like this.



  1. The Filter filters the rows in the range A2:D10 if E2:E10 is “Yes.”

  2. The Flatten makes four columns into one column.

  3. The Countif does the rest (counts the “Apple”).


Formula # 3:


=sum(ArrayFormula(if((A2:D10="Apple")*(E2:E10="Yes")=1,1,)))

The above is another formula to consider when we have varying array sizes to specify in Countifs in Google Sheets.


How does this formula return the count of values conditionally from multiple mismatching columns?


Here is how!


The logical test, the formula enclosed within SUM, returns the # 1 wherever both conditions match. Please see the below illustration.


Mismatching Columns in Criteria Ranges in Countifs

The SUM function returns the sum of those values.


If you have doubts about the array formula that I have used within SUM, you can learn that here – How to Use IF, AND, OR in Array in Google Sheets.


It’s just an alternative way to code AND logical test in array form.


I hope you could understand the above tips.


Thanks for the stay. Enjoy!


Countif | Countifs Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!