Summing custom function results across multiple cells in Google Sheets

Other Questions Google Sheets
1

I’ve made a custom function in Google Sheets that gives a score based on cell content. It looks like this:

SCORE_CALC(cell) = 
IF(cell="Strongly disagree", 0,
IF(cell="Disagree", 1,
IF(cell="Slightly disagree", 2,
IF(cell="Slightly agree", 3,
IF(cell="Agree", 4,
IF(cell="Strongly agree", 5, "ERROR!"))))))

Right now, I can only use it on one cell at a time. I want to add up the scores for a range of cells without typing out each cell separately. I’ve tried stuff like SUM(SCORE_CALC(A2:A10)) and SUM(SCORE_CALC(A2):SCORE_CALC(A10)), but they don’t work.

Is there a way to make my function work with a range of cells? Or maybe a different approach to sum up the scores for multiple cells at once? Any help would be great!

2

During my experience, I found a workaround that might suit your needs. Instead of modifying your SCORE_CALC function to handle ranges, you can preserve it as is and create a separate helper function to process an array of cells. One approach is to write a new function called SCORE_CALC_ARRAY that accepts a range and returns the result of applying SCORE_CALC to that range via ARRAYFORMULA.

For example:

function SCORE_CALC_ARRAY(range) {
  return ArrayFormula(SCORE_CALC(range));
}

You can then sum the scores in your sheet with =SUM(SCORE_CALC_ARRAY(A2:A10)). This method efficiently computes the total score without altering your original function.

3

I’ve encountered a similar challenge in my work with custom functions. One approach that’s worked well for me is using the MAP function in combination with SUM. It goes something like this:

=SUM(MAP(A2:A10, LAMBDA(cell, SCORE_CALC(cell))))

This essentially applies your SCORE_CALC function to each cell in the range A2:A10, then sums up the results. It’s a bit more flexible than ARRAYFORMULA in some cases, especially with more complex custom functions.

Another trick I’ve found useful is creating a separate column with your SCORE_CALC function, then using SUMIF or SUMIFS to total up scores based on specific criteria. This can be handy if you need to analyze subsets of your data later on.

Hope this helps! Let us know if you need any clarification on implementing these methods.

4

hey charlieLion, have u tried using ARRAYFORMULA? it might do the trick. something like =SUM(ARRAYFORMULA(SCORE_CALC(A2:A10))) could work. it applies ur function to each cell in the range and then sums it up. give it a shot and lemme know if it helps!