Use QUERY instead of COUNTIFS in Google Sheets formula
I am using the current formula:
=COUNTIFS(Data1!$F$2:$F$373,$A2,Data1!$G$2:$G$373,B$1)+COUNTIFS(Data2!$F$2:$F$373,$A2,Data2!$G$2:$G$373,B$1)+COUNTIFS(Data3!$F$2:$F$373,$A2,Data3!$G$2:$G$373,B$1)+COUNTIFS(Data4!$F$2:$F$373,$A2,Data4!$G$2:$G$373,B$1)
The formula works but I am trying to keep it as short as possible because I will be adding more data tabs. Is there a better way to do this using QUERY or some other option?
Here is a link to the google spreadsheet: https://docs.google.com/spreadsheets/d/1rgsqGS3IUAplYE6l48VUVMuYDQYfpgcP6vhvcHezprs/edit#gid=0
I want the count of the "animal_type(column F)" for the listed "Date(column G)".
Thank you!
List all the tab_names in, let's say, Column_Z (as in screenshot) and then use this single formula in Cell_B2:
=map(A2:A,lambda(Σ,map(B1:Y1,lambda(Λ,if(or(Σ="",Λ=""),,reduce(,tocol(Z:Z,1),lambda(a,c,a+countifs(indirect(c&"!F:F"),Σ,indirect(c&"!G:G"),Λ))))))))
