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?

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

    JMB17 my information is looking for text and dates. I tried what you shared but it didn’t work so I thought about using transpose and used a managed name for the P20:P27 information. How would this handle with large amount of data? With the new update of excel this file slowed down a lot. 

    =SUM(COUNTIFS(Table1[Column1],TRANSPOSE(_1XXXX),Table1[Completion Status],$W$3,Table1[FSC Code],$AB4,Table1[Type],'BN SERVICES'!$AC4,Table1[PlanDate MaintCall],">"&'BN SERVICES'!$Q$3,Table1[PlanDate MaintCall],"<"&'BN SERVICES'!$Q$4))

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Oct 01, 2022

      cocolopez4 It looks like your countif formula should work, but look to Peter's post to make it better. If it's not working for you, then perhaps upload an example of your data (without sensitive information), your formula, what results you're getting, and what results you're expecting.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Sep 30, 2022

      cocolopez4 

      It looks to me as if you are on the right lines.  The TRANSPOSE is not needed with COUNTIFS, summing a row of results will give the same result as summing the same values as a column.  I got to

      = SUM(
    COUNTIFS(
      RawData[ColumnF], 'BN SERVICES'!P$20:P$27,
      RawData[ColumnL],  $W$4,
      RawData[ColumnAA], $AB3,
      RawData[ColumnC], 'BN SERVICES'!$AC3,
      RawData[ColumnO],  ">" & 'BN SERVICES'!$Q$3,
      RawData[ColumnO],  "<" & 'BN SERVICES'!$Q$4
   )
  )

      To optimise a COUNTIFS formula, the criteria that eliminate the most matches should come first as the function does not perform further checks when the first condition is not matched. 

Resources