Search code examples
google-sheetsgoogle-sheets-formulaarray-formulas

Array arguments countifs are of different size - Google sheets

I've seen another post about this already but it wont fix my issue. For a research I easily want to create an overview of the data with Google Sheets. At this moment I get a #VALUE error when I use a COUNTIFS function.

Link to spreadsheet

In sheet1 (Data) you can find the research data.
In sheet2 (Overview) I want to make the overview of the data.
In this case every row is the data of one respondent.
They were able to cross multiple answers at a question about the type of holidays they took in the last two years.
In column G:K the answers can be found.

I want to create an overview where I can see how many times people from different ages answered a specific question.

I use the formula:

=COUNTIFS(Data!$D$2:$D$7, $A2, Data!$I$2:$N, B$1)

Spreadsheet example

Hope that anyone is able to help me. Thanks!

Solution

  • In B2 of the second tab try

    =countif(ArrayFormula(flatten(Data!$B$2:$B$6&Data!$G$2:$K$6)), Overview!$A2&Overview!B$1)

    and fill down and to the right.

    See if that works?