Excel 2013 or Excel 2016 32bit is very slow

Hi all,

I got Book A and Book B
Book B is using sumif in reference to Book A, specifically on Pivot Table.
Book B has about 40k++ rows (and may extend beyond 50k++ rows), and 30 columns

Any idea why it was so slow when calculating?
Even when I saved it it takes few hours?

I'm using Windows 7 Professional 64 bit OS i5-6300U CPU @ 2.4Ghz with 8GB RAM...

Initially i was using Excel 2016 32-bits, but it was as worst as it can get
then I was asking my IT personnel to instal Excel 2013 as a temporary solutions..

I need advice on the root cause and action that I can try....
It's been a struggle to work....

Thanks

Regards
cks1026

Hours??

Are these two separate workbooks (rather than worksheets in the same workbook)?
Are they both open at the time of saving and it taking hours?

Difficult to say what the root cause could be without far more information on these workbooks. What sort of formulas, other than the SUMIF, are involved?
Is there conditional formatting? Are you referencing entire rows in your formulas or just the required number of rows?

I could go on with a whole list of questions but fear I'd still not be able to accurately diagnose the issue.
Perhaps a desensitised version of the workbook(s) would be useful to see.

BSB

Are you using SUMIF or SUMIFS?

40-50k row x 30 columns = 1.2 - 1.5m cells if every one of those contains a SUMIF(S) formula then that will be some slow processing.

Something that I noticed previously with range based functions, SUMIF(S), COUNTIF(S) etc, is that the efficiency declines progressively, the more formulas in the sheet, the longer each one takes to calculate I can't remember the point where it becomes noticable without running some tests or finding the old thread, but think it was below 100k.

If I remember correctly it was also worse if the ranges were expanding i.e. $A$1:$A1 where the number of rows increase as you fill down. Will have a look later, see if I can fid the old thread with the details of the tests we ran at the time.