Forum Discussion
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 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.
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?
6 Replies
- Share a sample workbook with dummy data.
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 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))
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.
- A document without sensitive information could help you get an answer. That looks confusing.
