The "sum of elements" function
This function is necessary when you have a column with data where both the number of elements and the names of these elements are entered. The usual sum function will not work here because each cell contains different text. Therefore, the introduction of the new ‘Sum of Elements’ function will be an extremely useful innovation. Function will count all of the numbers in a column. Here is one way to write it: =SUMELM(range);
Ярослав Пляшечник
shared this idea
The idea suggests different approach to the workflow. Looking for community feedback and votes.
-
AdminConstantine
(Admin, ONLYOFFICE)
commented
Interesting concept for a function. I must mention that you can create and use custom functions in the spreadsheet editor. For you particular case, you can use, for instance, something like that:
(function () {
/**
* Function that calculates only numbers in specified range
* @customfunction
* @param {string[][]} range Range of cells
* @returns {number} total Total of numbers in range
*/
function SUMELM(range) {
let total = 0;
range.forEach(cell => {
cell.forEach(value => {
var thenum = value.match(/\d/g);
thenum = thenum.join("");
thenum = Number(thenum);
total += thenum;
})
})
return total;
}
Api.AddCustomFunction(SUMELM);
})();Adding =SUMELM(@A1:B1) for the cells that contain mixed values, e.g. "V25", "25 Kg", etc., will calculate only numbers, the result will be 50.
Please note that using this function without @ in range will produce a spilled array, use @ to keep the result in a single cell.You can find more information on how to add or create custom functions in this article: https://www.onlyoffice.com/blog/2024/07/introducing-custom-functions-for-onlyoffice-macros
We will keep the suggestion open for any users that might be interested in such feature too.