Nested If Statement using Sum (Need to Optimize)

heylookitsme I can't say I reproduce this. Tables do have some perf hit, but it isn't too bad in the test I did (see test workbook attached).

Two sheets: A sheet with data in a table (10,000 rows) and the sumproduct formula

and an identical sheet with the same data, not in a table.

I ran VBA code doing this for both worksheets:

• Calc on auto
• Run 50 times:
• Filter the table using greater than 50 on the second column
• Unfilter the table
• Calc on manual
• Run 50 times:
• Filter the table using greater than 50 on the second column
• Unfilter the table

I get these results:

With calculation ON, filtering the table takes longer than filtering the range

With calculation OFF, filtering the table takes less than filtering the range

But the difference isn’t that shocking.

If I delete all formulas and rerun the code I get this:

Even now, there is a difference between having calc on or off, but now filtering the range is quicker in both situations, by about 20%.