Home > Net >  Google sheet countifs multiple criteria range with checkboxes
Google sheet countifs multiple criteria range with checkboxes

Time:02-21

I am trying to display itemname image once a checkbox is ticked in. If a checkbox is ticked twice, the item should be displayed two times - 3 times if all checkboxes for one item are ticked.

Current function doesn't work:

ifna(filter(BT!A5:B,BT!C5:C),if({1,1},char(999)));
ifna(filter(BT!A5:B,BT!D5:D),if({1,1},char(999)));
ifna(filter(BT!A5:B,BT!E5:E),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!I5:I),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!J5:J),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!K5:K),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!O5:O),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!P5:P),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!Q5:Q),if({1,1},char(999)));
ifna(filter(Hyjal!A5:B,Hyjal!C5:C),if({1,1},char(999)));
ifna(filter(Hyjal!A5:B,Hyjal!D5:D),if({1,1},char(999)));
ifna(filter(Hyjal!A5:B,Hyjal!E5:E),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!I5:I),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!J5:J),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!K5:K),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!O5:O),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!P5:P),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!U5:U),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!V5:V),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!W5:W),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!Q5:Q),if({1,1},char(999)))}, 
countifs(Hyjal!C:W,true,BT!C:W,true),,2,1)

If I split the function for each sheet and try it, it works.

ifna(filter(Hyjal!A5:B,Hyjal!C5:C),if({1,1},char(999)));
ifna(filter(Hyjal!A5:B,Hyjal!D5:D),if({1,1},char(999)));
ifna(filter(Hyjal!A5:B,Hyjal!E5:E),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!I5:I),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!J5:J),if({1,1},char(999))); 
ifna(filter(Hyjal!G5:H,Hyjal!K5:K),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!O5:O),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!P5:P),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!U5:U),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!V5:V),if({1,1},char(999))); 
ifna(filter(Hyjal!S5:T,Hyjal!W5:W),if({1,1},char(999))); 
ifna(filter(Hyjal!M5:N,Hyjal!Q5:Q),if({1,1},char(999)))}, 
countif(Hyjal!C5:W37,true),,2,1)

And for the sheet BT I used

ifna(filter(BT!A5:B,BT!C5:C),if({1,1},char(999)));
ifna(filter(BT!A5:B,BT!D5:D),if({1,1},char(999)));
ifna(filter(BT!A5:B,BT!E5:E),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!I5:I),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!J5:J),if({1,1},char(999))); 
ifna(filter(BT!G5:H,BT!K5:K),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!O5:O),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!P5:P),if({1,1},char(999))); 
ifna(filter(BT!M5:N,BT!Q5:Q),if({1,1},char(999)))}, 
countif(BT!C5:Q73,true),,2,1)

So, I tried to combine those 2 functions with countifs, changed the column criteria range to C:W in the formula but it only displays items from the sheet Hyjal once items from the Sheet BT are checked. Items from BT itself are not displayed.

enter image description here

  • Related