I'm trying to do a self replicating countifs. Basically, if Cell C6:C isn't empty, countifs in 3 ranges according to 3 distinct criteria My individual cell formula is

=if($C6<>"",countifs(data!$K$2:$K,$C6,data!$W$2:$W,"2022",data!$H$2:$H,$C$4),"")

But this isn't self replicating and I need to drag it down all the way down. Is there a way to make this a self replicating formula? I know Array formula doesn't quite work well with Contifs

I tried putting it in an IF formula like this:

=ARRAYFORMULA({"2022"; if(C6:C="","",countifs(data!$K$2:$K,$C6,data!$W$2:$W,"2022",data!$H$2:$H,$C$4))})

But I'm getting the result for C6 repeated all over the cells

Here is an example sheet. 2022 has the method I tried, 2019 the one suggested here on the bottom (that doesn't work) and 2018 has mu current solution which I'm trying to avoid.

The Transpose method might not work since I have almost 20K rows in the "data" sheet.

Thank you.

CodePudding user response：

Without seeing your data it is hard to advise. But if you want to result in array fashion means spill results automatically to each cell in resulting column then can try below formula-

=Arrayformula(IF(Data!$K$2:$K="",,COUNTIFS(Data!$K$2:$K,$C6,Data!$W$2:$W,2022,Data!$H$2:$H,$C$4, ROW(Data!$K$2:$K), "<="&ROW(Data!$K$2:$K))))

CodePudding user response：

My IF formula was actually right, I just forgot to make it run on C6:C instead of just C6

=ARRAYFORMULA({"2022"; if(C6:C="","",countifs(data!$K$2:$K,$C6,data!$W$2:$W,"2022",data!$H$2:$H,$C$4))})

This is the one that works:

=ARRAYFORMULA({"2022"; if(C6:C="","",countifs(data!$K$2:$K,$C6:C,data!$W$2:$W,"2022",data!$H$2:$H,$C$4))}) Thank you to Harun24hr for the help with seeing this.