Help speed/clean up frequency formula

Hi,

My formula is counting unique values based on multiple criteria and I've used the frequency formula but I believe because my data set is so large it makes my array huge and so it takes a lot of time and memory to process the formula. Is there a better way to write this formula or calc something off my data set to help? Thanks!!

=SUM(IF(FREQUENCY(IF('CC Data Translation'!$C:$C=$B47,IF('CC Data Translation'!$M:$M=BW$11,MATCH('CC Data Translation'!$L:$L,'CC Data Translation'!$L:$L,IF('CC Data Translation'!$M:$M=BX$11,MATCH('CC Data Translation'!$L:$L,'CC Data Translation'!$L:$L,IF('CC Data Translation'!$M:$M=BY$11,MATCH('CC Data Translation'!$L:$L,'CC Data Translation'!$L:$L,0))))))),ROW('CC Data Translation'!$L$5:$L$80000)-ROW('CC Data Translation'!$L$5)+1),1))

Once you revise your ranges, per TMS, you can also consider consolidating your "OR" M-range tests given the contiguous criteria range

=SUM(IF(FREQUENCY(IF(C-range=x,IF(ISNUMBER(MATCH(m-range,BW$11:BY$11,0)),MATCH(L-range,L-range,0))),ROW(..)-ROW(...)+1),1))

whilst the above will have a negligible impact on performance it will shorten / simplify the formula itself.

if you have access to the FILTER & UNIQUE functions, given your O365 subsc, you might consider looking into that...

I don't unfortunately but, if we assume these will mimic the GoogleSheets equivalent then something like below might work?

=COUNTIF(UNIQUE(FILTER(L-range,(C-range=x)*ISNUMBER(MATCH(M-range,BW$11:BY$11,0)))),"?*")

and, if that's true, I would hope the above will prove (far) more efficient with large ranges than an iterative Array.