Forum Discussion

cocolopez4's avatar
cocolopez4
Copper Contributor
Sep 30, 2022
Solved

Help me simplify countifs

=SUM(COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$20,'RAW DATA'!$L:$L,$W$3,'RAW DATA'!$AA:$AA,$AB3,'RAW DATA'!$C:$C,'BN SERVICES'!$AC3,'RAW DATA'!$O:$O,">"&'BN SERVICES'!$Q$3,'RAW DATA'!$O:$O,"<"&'BN SE...
excel
Formulas and Functions
office 365
  • JMB17's avatar
    JMB17
    Sep 30, 2022

    I believe you could shorten the formula to (assuming I'm correct that the countifs functions are all the same except for the first argument testing column F against P20, P21, ..., P27):


    =SUM(('RAW DATA'!$F:$F=transpose('BN SERVICES'!P$20:P$27)) * ('RAW DATA'!$L:$L=$W$3) * ('RAW DATA'!$AA:$AA=$AB3) * ('RAW DATA'!$C:$C='BN SERVICES'!$AC3) * ('RAW DATA'!$O:$O>'BN SERVICES'!$Q$3) * ('RAW DATA'!$O:$O<'BN SERVICES'!$Q$4))

    But, to improve the calculation lag you really should try to avoid using entire column references (over 1 million cells per column that have to be calculated). Can the data be put into a table and use structured table references?

JMB17's avatar
JMB17
Bronze Contributor
Sep 30, 2022

I believe you could shorten the formula to (assuming I'm correct that the countifs functions are all the same except for the first argument testing column F against P20, P21, ..., P27):


=SUM(('RAW DATA'!$F:$F=transpose('BN SERVICES'!P$20:P$27)) * ('RAW DATA'!$L:$L=$W$3) * ('RAW DATA'!$AA:$AA=$AB3) * ('RAW DATA'!$C:$C='BN SERVICES'!$AC3) * ('RAW DATA'!$O:$O>'BN SERVICES'!$Q$3) * ('RAW DATA'!$O:$O<'BN SERVICES'!$Q$4))

But, to improve the calculation lag you really should try to avoid using entire column references (over 1 million cells per column that have to be calculated). Can the data be put into a table and use structured table references?

Resources