How to simplify this formula?

[BrettOlbrys1]

=IF(B3="","",IF(Q3>0,COUNTA(UNIQUE(FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB, ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=H3), ""))),0) +
IF(R3>0,COUNTA(UNIQUE(FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB, ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=I3), ""))),0) +
IF(S3>0,COUNTA(UNIQUE(FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB, ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=J3), ""))),0) +
IF(T3>0,COUNTA(UNIQUE(FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB, ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=K3), ""))),0)

Other than the beginning IF statement, all of the statements are the exact same except for the BOLDED items. How can I shorten this formula?

 

[KRice]

Here is a start...you can use the LET function to assign the repeated elements to short-name variables.

=LET(
colAB,'Table - Acts Combined_SOBO_2023'!$AB:$AB,
colB,'Table - Acts Combined_SOBO_2023'!$B:$B,
colO,'Table - Acts Combined_SOBO_2023'!$O:$O,
colI,'Table - Acts Combined_SOBO_2023'!$I:$I,
BOfltr,(colB>=$B$1)*(colB<$B$2)*(colO="VALIDATED"),
IF(B3="","",
IF(Q3>0,COUNTA(UNIQUE(FILTER(colAB, BOfltr*(colI=H3), "")))) +
IF(R3>0,COUNTA(UNIQUE(FILTER(colAB, BOfltr*(colI=I3), "")))) +
IF(S3>0,COUNTA(UNIQUE(FILTER(colAB, BOfltr*(colI=J3), "")))) +
IF(T3>0,COUNTA(UNIQUE(FILTER(colAB, BOfltr*(colI=K3), ""))))
))

But let me ask...are you certain of the logic in the last step? That has the potential to double count rows, if for example the H3 and I3 conditions are both satisfied (which means H3=I3=column I) when the Q3 and R3 criteria are met (then you'll count the same row twice). That may be okay...I don't know what your data look like.

 

[Cubist]

Perhaps...

=SUM(
     (Q3:T3>0)*
          DROP(
               REDUCE("",H3:K3,LAMBDA(a,b,HSTACK(a,COUNTA(UNIQUE(
                    FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB,
                         ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*
                         ('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=b),0)))))),,1)
)

 

[Cubist]

Or this. Should do the same as #3 but shorter.

=SUM(
     (Q3:T3>0)* BYCOL(H3:K3,LAMBDA(b,COUNTA(UNIQUE(
           FILTER('Table - Acts Combined_SOBO_2023'!$AB:$AB,
                 ('Table - Acts Combined_SOBO_2023'!$B:$B>=$B$1)*('Table - Acts Combined_SOBO_2023'!$B:$B<$B$2)*
                         ('Table - Acts Combined_SOBO_2023'!$O:$O="VALIDATED")*('Table - Acts Combined_SOBO_2023'!$I:$I=b),0))))))