AppleScript Numbers: design options for performance

Environment:

• Mojave , Numbers 6.1, Automator 2.9
• Both Numbers and Automator are set via System Preferences > Security & Privacy > Accessibility

Background:

I have a spreadsheet for finances in which one sheet ("Monthly Summary") has ~40 rows of data categorization (e.g.: medical expenses, groceries, transportation, etc.) and N columns of months for 4.5 years (currently, e.g.: 54 columns and growing).

The data is pulled from another sheet ("Details") which has many rows (over 4000) with 3 columns:

• • Date
  • Amount
  • Categorization Key

My current Service script design uses =SUMIFS(...) formulas in each cell of the "Monthly Summary" table to extract and sum-up the relevant data for that cell based on the row's categorization key and the columns year/month date specification.

Issue:

Numbers sometimes stalls for quite a while "calculating".

I'm wondering if alternate design might be better.

Option 1: Current Design

As indicated, right now I have this massive sheet with over 2000 =SUMIFS(...) inserted into it.

• • • Pros: Already set up as such; Code already written
    • Cons: Periodic usability delays when Numbers decides to start calculations - press,ably for each formula encountered in the spreadsheet.

Option 2: Separate Tables Per Year

Someone suggested I have multiple tables on a sheet, one for each year so that any single table would have about 500 =SUMIFS(...) in it.

• • • Pros: Not sure, perhaps by having less formulas per table the calculations take less time?
    • Cons: Personally I find it confusing to navigate multiple tables on a single sheet

Option 3: Separate Sheets Per Year

This is essentially the same as Option 2 except that I have separate sheets with one table each, for each year, but still about 500 =SUMIFS(...) on each sheet / table.

• • • Pros: Not sure, perhaps by having less formulas per sheet/table the calculations take less time?
    • Cons: Not sure. A bit harder to visually scan the "big picture" because you have to flip back-and-forth between sheets.

Option 4: Copy Formula Results Over Formula

From my own trials I discoed that once I set the formula in the cell, it gets calculated, and I can then copy the value of the cell over itself - e.g.:

 ...
 set value of targetCell to regexString
 set value of targetCell to value of targetCell
 set the format of targetCell to currency
 ...

• • • Pros: Complete elimination of formulas in the spreadsheet; can keep all data on one sheet or break it up into multiple sheets / tables as seems reasonable.
    • Cons: The "Monthly Summary" data would not be automatically kept up-to-date as more data is entered into the "Details" sheet, so the Service script would need to be re-run periodically.

Option 5: Perform Calculations In Script Instead of Formulas

I haven't looked into this too closely yet, mostly because I'm not that comfortable doing full-out programming in AppleScript (I have 20+ years experience writing Perl code and I often find it difficult to change my coding concepts to AppleScript structures - if possible) - but essentially I would perform all the same functions that the =SUMIFS(...) formula is doing in "pure code" and then insert the result into each cell .

• • • Pros: Complete elimination of formulas in the spreadsheet; can keep all data on one sheet or break it up into multiple sheets / tables as seems reasonable.
    • Cons: The "Monthly Summary" data would not be automatically kept up-to-date as more data is entered into the "Details" sheet, so the Service script would need to be re-run periodically; It is also conceivable that the amount of time it takes to perform the equivalent of the formula internally may equal, or even exceed the amount of time that Numbers now spends "calculating" the results of the existing formulas.

At this point I'm interested in opinions - pros & cons, etc.

I'm going to move ahead with Option 1 because I have it pretty much ready to go, and Option 4 is a trivial two-line addition to the script, but I'm curious to hear what other's think to see if I'm overlooking something.