How to apply a formula in each row using ArrayFormula (maybe?) in Google Spreadsheets

0
votes

I need to do a sheet to managing some informations in my job.

I'd like to make a compilation of informations filtred by month, i.e., I'd like to insert a month (B1 cell) and all the informations would be refreshed by this month.

For while I could manage this data:

Team (Equipe); Modules (Módulos); and Type (Tipo).

and my formula is working:

=COUNTIFS(Geral!$B$2:$B$45;$A5;Geral!$C$2:$C$45;$B5;Geral!$E$2:$E$45;C$4).

What I'd like to do is something like this:

=COUNTIFS(Geral!$B$2:$B$45;$A5;Geral!$C$2:$C$45;$B5;Geral!$E$2:$E$45;C$4;Geral!$F$2:$F$45;MONTH(B1)).

I know this formula is wrong, but I don't know how to do this... I've already tried to use arrayformula, but it didn't work...

How do I do this?

Here is the link to the sheet:

https://docs.google.com/spreadsheets/d/1NOkvkJ7B3lGcSaUTtUdrnbmdd8AjiRvniH2lM3kASSU/edit?usp=sharing

Thanks in advance!

1
google-sheetsarray-formulas

1 Answers

1
votes

I believe what you need is:

=ArrayFormula(COUNTIFS(Geral!$B$2:$B$45;$A5;Geral!$C$2:$C$45;$B5;Geral!$E$2:$E$45;C$4;MONTH(Geral!$F$2:$F$45);B1))

... but beware that date functions in Sheets will read a blank cell as 30/12/1899, therefore MONTH() on a blank cell will return 12. So you might want to include a test to rule out blank-ness:

=ArrayFormula(COUNTIFS(Geral!$B$2:$B$45;$A5;Geral!$C$2:$C$45;$B5;Geral!$E$2:$E$45;C$4;MONTH(Geral!$F$2:$F$45);B1;Geral!$F$2:$F$45;"<>"))