SUMPRODUCT formula does not work when first opening Excel file

Hi there,

I have a helper column to determine which group (fruits or vegetables, in this sample sheet) each item belongs to in a list. It uses SUMPRODUCT, MAX, and ROW to look up the last group header that occurs before the item itself.

The helper column is in column A.

The lists on the Lists tab are then generated dynamically (and used elsewhere in my actual spreadsheet for data validation dropdown purposes)

I realize it would be much simpler to have the fruit Group next to the vegetable Group but it has to stay in this vertical format.

It all works well but when I save and reopen the workbook, all the SUMPRODUCT formulas say #VALUE!

If I open just one of those formulas and hit enter, every single SUMPRODUCT then calculates correctly

Open to suggestions for how to fix the SUMPRODUCT issue or use a different formula, but cannot change the format of how the items are listed or grouped at this time

I did this thanks