0

I have a large spreadsheet with I think 1000 formulas and I want to move them to an older version of the Google Sheet, because of the enhanced protection options I have there. (Basically I need to keep some sheets only for some people, while the rest to all with the link).

The tabels are not mine, so I don't know the logic of them - I just need to port them to the older version. Example of a formula is:

=SUMIFS('FC Eingabe'!$K$2:$K$142,'FC Eingabe'!$R$2:$R$142,"August",'FC Eingabe'!$S$2:$S$142,"100% | Auftrag erteilt")

Changing the SUMIFS to a solution from SUMIFS function in Google Spreadsheet is too time consuming to be worth it.

Is there any way I could add something like a custom function SUMIFS to that old Google Sheet? - something like a VBA piece of code, maybe. Or an add-on.

Please help!

Community
  • 1
  • 1
Irina Sescu
  • 11
  • 2

1 Answers1

0

Here is a quick and dirty (no error checking etc) attempt at a custom function:

function SUMIFS()
{
  var test, result = 0;
  var sumArray = arguments[0];
  for (var i = 0, length = sumArray.length; i < length; i++)
  {
    test = true;
    for (var j = 1, argLength = arguments.length; j < argLength; j+=2)
      test = test && (arguments[j][i][0] == arguments[j+1]);
    if (test) result += sumArray[i][0];
  }
  return result;
}

Alternatively, you could use a Find and replace (accessed with Ctrl+H) in the current Sheets version. It must be done in the current version as finding and replacing inside formulae is not supported in the old version.

This should work for two conditions, as in your OP:

Find:           ^=SUMIFS(\(.+?,.+),(.+?,.+),(.+?\))$
Replace with:   =SUM(FILTER$1=$2=$3)

and ensure "search using regular expressions" and "also search within formula" are checked.

AdamL
  • 23,691
  • 6
  • 68
  • 59