I made a Google Sheet to track some metrics at my job. In a simple explanation, I create annotations (or tracks) on images and submit it for review. If it is reviewed and sent back to me, I fix it and add whatever I missed and send it back.

In order to track my progress, I wanted to create a formula that tracks how many total tracks I missed for each task (most recent attempt - earliest attempt). It took some finagling, but I finally got a formula that works. My only complaint is that it works extremely slow. Here is my formula:

=ARRAYFORMULA({"Number of Tracks Missed"; IF(NOT(ISBLANK($F$2:$F)), IF($G$2:$G = 1, 0, (VLOOKUP($F$2:$F,SORT($B$2:$C, $A$2:$A, FALSE), 2, FALSE)) - (VLOOKUP($F$2:$F, $B$2:$C, 2, FALSE))), "")})

Column G is a count of how many times I have worked on a task, so if I've only had it once, then I didn't miss anything so that equates to 0. If it's been more than once, I use a complicated VLOOKUP combined with a SORT that flips the list of my tasks to the reverse order, so the VLOOKUP will find the most recent attempt, then subtract my earliest attempt from it.

Like I said, this formula works and gives the correct response every time, it's just super slow. Is there a way I can speed this calculation up, or is this the best/only option I have? I'm somewhat new to using formulas like this, so any help is appreciated.

EDIT:

Thanks to player0 for pointing me in the right direction. My formula was fine, the problem was all the extra rows I had in the sheet. For some reason, There were 32,000 rows. With the Arrayformula calculating for every single one, even if it's just with blanks, it was taking a long time.

As soon as I deleted the extras, it now calculates almost immediately.

CodePudding user response：

somewhere along the path of creation of your sheet an error occurred and added 32k of empty rows to the sheet which caused you that decreased response.

if you don't need so many rows - delete them. your formula is fine.