Sep 29 2022 10:00 PM
=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.
Sep 29 2022 10:25 PM
Sep 29 2022 10:29 PM - edited Sep 29 2022 10:31 PM
SolutionI 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?
Sep 30 2022 04:36 AM
@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))
Sep 30 2022 05:55 AM
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.
Sep 30 2022 06:56 PM
@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.
Sep 29 2022 10:29 PM - edited Sep 29 2022 10:31 PM
SolutionI 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?