Only Run Formula on Cells in The Table.

Timberwolf

New Member
Joined
Feb 20, 2018
Messages
26
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")
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,377
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top