I have a spreadsheet with hundreds of thousands of rows across several sheets, using several columns of VLOOKUPs on each sheet, plus some columns of formulas like COUNTIFS and SUMIFS that look at every row on a sheet whenever they calculate. It spikes my CPU every time it calculates, and sometimes it takes so long I can brew a cup of coffee before it's done.
I've already made it a lot less terrible by using MATCH("*",[column],-1) to find the last row and dynamically generate the range instead of just referencing the entire column, and by replacing the VLOOKUPs with INDEX MATCHes. There are just two formulas left that I can't figure out how to improve.
Here's an approximation of the data I'm working with:
+--------+-------+----------+--------+ | Group | Item | Quantity | Round | +--------+-------+----------+--------+ | Group1 | Item1 | 2 | 1 | | Group1 | Item2 | 2 | 2 | | Group2 | Item1 | 2 | 1 | | Group2 | Item2 | 3 | 2 | | Group2 | Item3 | 2 | unused | +--------+-------+----------+--------+
For the first problem, I want to see if all items in the group have the same quantity. The old version used this formula:
Can I accomplish the same thing without using COUNTIFS? Is working with a pivot of the data likely to improve performance over working with the raw data itself? Or is the best I can do to replace the entire-column references with the MATCH-calculated dynamic ranges?
For the second problem, I'm trying to find the minimum "round" value for the group. Old formula:
It used to be an array formula -
‑ so I was super excited when MINIFS was added to Excel. But is there something better than MINIFS that I can use to get this answer?