1

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:

=COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,C:C,C2)

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:

=MINIFS(D:D,A:A,A2)

It used to be an array formula -

={MIN(IF(A:A,A2),IF(P:P>0,P:P))}

‑ 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?

  • This answer might not realistically apply to your situation but it is short and it might hold a grain or two of truth: "a Database". If you have the time, take a look at SQL (or maybe a different flavour). You can get MS SQL Express to play around with for free. It is most likely much more suited to this kind of problem and amount of data. MS Access might be an option too. – TheUser1024 Feb 15 '17 at 21:12
  • Oh man, the answer to all of my problems related to this project is "a database". We're working on it. Unfortunately we have some crap data upstream, and these formulas are to find and correct the crap so we can move on to the next part of the process. – Shadowsong Feb 16 '17 at 0:19
0

You can totally do this with a pivot table. In my experience a pivot table (once created and cached) is much faster than sumif/countif formulas, but it depends on the size of your data set.

Create a pivot table on your data as follows:

  • Select your data, click the Pivot Table button, click OK
  • Drag "Group" to the rows box
  • Drag "Round" to the columns box, click it, click "Value field settings", select "Min" and click OK - there's your minimum for each group
  • Drag "Quantity" to the columns box, click it, click "Value field settings", select "Min" and click OK
  • Drag "Quantity" to the columns box, click it, click "Value field settings", select "Max" and click OK

If "Min of Quantity" and "Max of Quantity" are equal, then you know all items in the group have the same quantity. You can create a formula off to the side of the pivot table to evaluate this for you.

Screenshot of how it should look in Excel

  • That looks perfect, thanks! I always forget that there are "summarize values by" options other than just "sum" and "count". – Shadowsong Feb 16 '17 at 23:53

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.