Forum Discussion
Solved
Calculating (12 Threads): 0%
Good morning! Office 365 user. With the addition of a few new formulas, my spreadsheet has become very laggy. I now see the above subject line in the status bar of my application. We're talki...
- I concur. The killer here is B:B / C:C. Observed this myself only 2 days ago.
Thank you. The latest formula that really put me over the edge is:
=SUM(--(IF(SITES!B:B=B2,COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C:C),0)>1))
I assume COUNTIFS are equally problematic to IFS?
I also have a large number of formulas on one of my tabs that references entire columns (B:B, for instance). Thanks again.
=SUM(--(IF(SITES!B:B=B2,COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C:C),0)>1))
I assume COUNTIFS are equally problematic to IFS?
I also have a large number of formulas on one of my tabs that references entire columns (B:B, for instance). Thanks again.
That formula looks particular suspect. the COUNTIFS:
COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C:C)
looks like it may be doing an array of arrays as you conditional is an array. Do you REALLY want SITES!C:C to be checked for the whole column of C or just C2 like you are doing for column B?
i.e.:
COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C2)
I also HIGHLY recommend defining the data range as a table (HOME -> Format as a Table) if possible so you can then use Table references INSTEAD of using the entire column so something like:
COUNTIFS(SITEtable[Col B],B2,SITEtable[Col C],C2)
- I concur. The killer here is B:B / C:C. Observed this myself only 2 days ago.
- This help is HUGE! Thank you. Fixed, and running 100 X faster. Now I will go through and convert the rest of the similar references. Appreciate you!!!!