CODEWITHSUNDEEP
google-sheets
altschuler
altschuler

Reputation: 3922

Google Spreadsheet: ArrayFormula and Filter

I realize that there are multiple questions with this title, but I don't understand the answers to them, or it is not directly related to my issue.

I'm trying to avoid manually "dragging" a formula to duplicate it for each row.

Formula looks like this

=IF(ISBLANK(A6), 
    "", 
    COUNT(FILTER(Data!C$2:C, 
                 Data!A$2:A = A6, 
                 Data!B$2:B >= B$2, 
                 Data!B$2:B <= B$3, 
                 Data!C$2:C >= E$3, 
                 Data!C$2:C <= E$2)))

I'm not sure all the anchors are needed when using ARRAYFORMULA.

The A column is the one I want to "interate", the rest of the ranges should evaluate to whatever they're evaluating to now.

I have tried this:

=ARRAYFORMULA(IF(ISBLANK(A6:A), 
              "", 
              COUNT(FILTER(Data!C$2:C, 
                           Data!A$2:A = A6:A, 
                           Data!B$2:B >= B$2, 
                           Data!B$2:B <= B$3, 
                           Data!C$2:C >= E$3,
                           Data!C$2:C <= E$2))))

In which the A6:A seems to evaluate correctly to each cell in the A column. However, the ranges from the Data sheet also seem to evaluate to a single value and not the ranges.

Am I missing something or do I not understand how ARRAYFORMULA works?

Upvotes: 0

Views: 4332

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

filter is an arrayformula by itself → it can't be used in another arrayFormula because Sheets cannot handle array of arrays.

Workaround

You are looking for countifs arrayFormula, which is solved with mmult:

=arrayformula(TRANSPOSE(MMULT(TRANSPOSE((Data!B$2:B>=B2)*(Data!B$2:B<=B3)*(Data!C$2:C<=E2)*(Data!C$2:C>=E3)),--(Data!A$2:A=TRANSPOSE(A6:A10)))))

Explanation

=arrayformula(TRANSPOSE(MMULT(...,...) ^^^^^ mmult will count for 1's and 0's in intersection of conditions

...TRANSPOSE((Data!B$2:B>=B2)*(Data!B$2:B<=B3)*(Data!C$2:C<=E2)*(Data!C$2:C>=E3))... ^^^^ any number of 'plain' conditions goes here

...--(Data!A$2:A=TRANSPOSE(A6:A10)... ^^^^ all 'filter' conditions go here, you have only 1 of those

Upvotes: 4

Related Questions