Convert Countifs to an array formula-CodePudding

I have set up this table

I have 4 columns for each color (red, green, yellow, blue) for a total of 16 columns. If 2 of the 4 red columns contain a value of “4”, I want the formula to output “2”.

The following formula does exactly this (see column Q in sheet) It first checks, if the column is named “red” and then checks if the specific row contains a value of “4”

=COUNTIFS($A$1:$P$1; "red"; A4:P4; 4)

Since I have the sheet connected to a Google Forms, I need to convert this formula to an ArrayFormular to automatically have the fields populate each time it receives a new submission. The setup using Google Forms also prevents me from using a smarter table setup.

I already tried a solution using the query() feature, but can't make it work.

Thankful for any suggestions

CodePudding user response：

Here is a formula that should work for you:

={"how many number '4' in  the red columns?";ArrayFormula(if(index(filter(A2:P;A1:P1="Red");;1)="";;dcount(transpose({SEQUENCE(index(filter(A2:P;A1:P1="Red");;1))\if(filter(A2:P;A1:P1="Red")=4;filter(A2:P;A1:P1="Red");)});sequence(rows(index(filter(A2:P;A1:P1="Red");;1)));{if(;;);if(;;)})))}

It can be placed in cell Q1 in your sheet.

I wrote this formula with my personal locale settings, and switched them to your spreadsheet settings once I got it working. The above formula should work in your sheet.

Please let me know if you have any issues.

For anyone within the United States (or with similar locale settings on their sheet), here is the same main formula with the adjusted separators:

=ArrayFormula(if(index(filter(A2:P,A1:P1="Red"),,1)="",,dcount(transpose({SEQUENCE(index(filter(A2:P,A1:P1="Red"),,1)),if(filter(A2:P,A1:P1="Red")=4,filter(A2:P,A1:P1="Red"),)}),sequence(rows(index(filter(A2:P,A1:P1="Red"),,1))),{if(,,);if(,,)})))

Reference:
https://infoinspired.com/google-docs/spreadsheet/countif-across-columns-row-by-row-google-sheets/

CodePudding user response：

using countifs in array is difficult task but you can use this =ArrayFormula(if(index(filter(A2:P,A1:P1="Red"),,1)="",,dcount(transpose({SEQUENCE(index(filter(A2:P,A1:P1="Red"),,1)),if(filter(A2:P,A1:P1="Red")=4,filter(A2:P,A1:P1="Red"),)}),sequence(rows(index(filter(A2:P,A1:P1="Red"),,1))),{if(,,);if(,,)})))