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!
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;"<>"))