Help me simplify countifs

[cocolopez4]

=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 SERVICES'!$Q$4),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$21,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$22,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$23,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$24,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$25,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!P$26,'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),COUNTIFS('RAW DATA'!$F:$F,'BN SERVICES'!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))

Can anyone please tell me if there’s a simpler way to do this? The file is already large and runs slower than I like. 

[#2]

A document without sensitive information could help you get an answer. That looks confusing.

[#3 · Accepted solution]

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?

[#4]

Share a sample workbook with dummy data.

[#5]

@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))

[#6]

@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. 

[#7]

@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.