Search code examples
google-sheetsarray-formulasgoogle-sheets-formula

Google Spreadsheet, use array formula with filter betwen date

Hy everyone, I try a simple trick, Use an array formula with a countblank with a filter between date. Without array, its work great, with an array, it goes crazy. why?

I use a spreadsheet to follow the present or absent off people by month. (exemple sheet) I try to like this by line and its work fine: 

=COUNTBLANK(FILTER(D9:AU9,D8:AU8>=B4,D8:AU8<=B5))

When i try to apply this to every line, its goes crazy : 

=ARRAYFORMULA(IF(A8:A19<>"",COUNTBLANK(FILTER(D8:AU,D8:AU8>=B4,D8:AU8<=B5)),""))

Where is my mistake?

Solution

  • In C8 try this formula

    ={""; Arrayformula( if(len(A9:A), countif(if((D9:AU="")*(month($D$8:$8)=month($B$3)), row(A9:A)), row(A9:A)),))}

    and see if that works?