Formula stop a calculation running once it has reached a defined criteria

Hello all,

Long time reader and user of this forum but little poster

I have a problem i am hoping someone can advise upon.

I have a very large data set across a number of Tabs which requires a Sumif across all the tabs. Unfortunately, the look up criteria is not set, so could be looking up 10 items across the numerous tabs, or could be looking up 100 items.

I currently have it looking up a max of 100 items, but this is user defined and unlikely to be this many, so a simple max formula can determine the end of the data set to look up. The problem i have is the data set is consderable, so running this formula 100 times on 100 rows uses a vast amount of processing.

Ideally i want the formula to stop running the Sumif once it has reached the last row of data to look for (if that makes sense - the user might have only entered values to look up for 30 rows, and therefore want the formula to stop processing on 30). I am sure i have seen this somewhere before, but cant find it now...

If anyone has any solutions, that would be great thank you.

Example worksheet attached.