Only Run Formula on Cells in The Table.

[Timberwolf]

Hi all!

When I open my excel book it takes forever to open due to poor coding on my part. I have a bunch of codes like the below code. What I want is instead of E:E which I believe is why it takes so long to open I only want it to check cells in the actual table or until it hits a blank. I understand I could use E1:E10000 but then I would have to keep upping the "E10000" value. Any thoughts?

=COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!$I:$I,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!L:L,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!O:O,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!R:R,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!U:U,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!X:X,"Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!AA:AA,"Yes")

 

[DanteAmor]

To improve the performance of your sheet you must change E:E for something like E2:E1000, also O:O, AA:AA, etc.
If you put 1000 and your range changes to 1001, it is advisable to change to something like 1100.
But don't increase beyond that.
Putting a formula that looks for the last value with data from the table also decreases the performance of the sheet.


To improve the performance of your sheet, also delete rows where you have the formula and still do not occupy, or validations that do not occupy or format in cells that do not occupy.