Search code examples
arraysgoogle-sheetscountifgoogle-sheets-formulagoogle-sheets-query

Alternative to QUERY nested in COUNTIF for column array in Google Sheets

Currently I have a formula which is working very well, however, it is making my sheets extremely slow as I am using more than one of these COUNTIF queries.

 =IF($O$1="YT",COUNTIFS(
 QUERY(Contacts!$Q$3:$EZ, "select V,AC,AJ,AQ,AX,BE,BL,BS, BZ ,CG,CN,CU,DB,DI,DP,DW,ED,EK,ER,EY", 0), $T22,       
 QUERY(Contacts!$Q$3:$EZ, "select U,AB,AI,AP,AW,BD,BK,BR,`BY`,CF,CM,CT,DA,DH,DO,DV,EC,EJ,EQ,EX", 0), U$21),    
 COUNTIFS(
 QUERY(Contacts!$Q$3:$EZ, "select T,AA,AH,AO,AV,BC,BJ,BQ, BX ,CE,CL,CS,CZ,DG,DN,DU,EB,EI,EP,EW", 0), $O$1,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 QUERY(Contacts!$Q$3:$EZ, "select V,AC,AJ,AQ,AX,BE,BL,BS, BZ ,CG,CN,CU,DB,DI,DP,DW,ED,EK,ER,EY", 0), $T22,      
 QUERY(Contacts!$Q$3:$EZ, "select U,AB,AI,AP,AW,BD,BK,BR,`BY`,CF,CM,CT,DA,DH,DO,DV,EC,EJ,EQ,EX", 0), U$21))

Basically what this does is search the month from a drop-down in the dashboard, and then counts the number of calls by filtering Call Category, Sales Person and Month. The reason I have 2 COUNTIF's is that the first calculates based on the whole year hence YT = "Year Total"

The calls come from an array of columns, hence the query for different columns. Any feedback would be much appreciated.

I'm more proficient with functions than I am with app script, but always willing to learn, so if there is an app script solution I would be more than grateful to learn.

I look forward to learning from you.

Solution

  • you will need to kill all your query formulas (all those which looks like the example in your question) and replace them with this build:

    =IF($G$2="Year Total",
 COUNTA(FILTER(Contacts!V3:V,  Contacts!V3:V=$B12,  Contacts!U3:U=E$11))+
 COUNTA(FILTER(Contacts!AC3:AC,Contacts!AC3:AC=$B12,Contacts!AB3:AB=E$11))+
 etc...


,COUNTA(FILTER(Contacts!T3:T,  Contacts!T3:T=$G$2,  Contacts!V3:V=$B12,  Contacts!U3:U=E$11))+
 COUNTA(FILTER(Contacts!AA3:AA,Contacts!AA3:AA=$G$2,Contacts!AC3:AC=$B12,Contacts!AB3:AB=E$11))+
 etc...

 )

    0

    this will give you ~80% boost