Hello, everyone! Today I want to share a super useful script I created to solve a common problem in daily life.
If you've ever tried summing "duration" in Google Sheets, you might have noticed that the SUMIF and SUMIFS formulas don't work to sum durations of events or products based on specific criteria. This can be an obstacle depending on the type of calculation you need to do. But don't worry! Google Sheets allows you to create scripts in JavaScript and use them as custom formulas.
In my script, I created two variations: the first one accepts one criterion and the second one accepts up to two. I'm planning to improve this in the future to make the function even more flexible.
It's worth mentioning that custom formulas don't return values directly computable by the program. To work around this, you can wrap the result with the =VALUE() function. Then, just apply the corresponding formatting to the data type—in our case, "duration". Ready to check out the script?
First of all, let's generate data to test the formula. I used our friend GPT for this.
| Title | Duration | Category | Status |
|---|---|---|---|
| The Martian | 01:00:00 | Movie | Watched |
| Interstellar | 02:49:00 | Movie | Watched |
| John Wick | 01:30:00 | Movie | Watched |
| Avengers: Endgame | 03:00:00 | Movie | Want to watch |
| Stranger Things | 00:45:00 | Series | Watching |
| The Witcher | 01:00:01 | Series | Watching |
| The Mandalorian | 00:40:00 | Series | Watching |
| Breaking Bad | 00:50:00 | Series | Watched |
| Money Heist | 00:55:00 | Series | Want to watch |
| Game of Thrones | 01:10:00 | Series | Want to watch |
I tried to document everything as best as possible. I decided to break it down into smaller functions and use something more declarative to increase code clarity.
function allAreArrays(...arrays) {
return arrays.every(Array.isArray);
}
function allArraysHaveSameLength(...arrays) {
const lengths = arrays.map((arr) => arr.length);
return lengths.every((val) => val === lengths[0]);
}
function convertHMSToSeconds(hms) {
// Breaks the string in HH:MM:SS format into parts
const parts = String(hms).split(":");
// Converts the parts into integers
const [hours, minutes, seconds] = parts;
// Converts hours and minutes into seconds and adds the seconds
const totalSeconds =
Number(hours) * 3600 + Number(minutes) * 60 + Number(seconds);
return Number(totalSeconds);
}
function convertSecondsToHMS(seconds) {
// Calculates the number of hours, minutes, and seconds
const hours = Math.floor(seconds / 3600);
const minutes = Math.floor((seconds % 3600) / 60);
const remainingSeconds = seconds % 60;
// Adds a leading zero to ensure it always has two digits
const hourFormat = String(hours).padStart(2, "0");
const minuteFormat = String(minutes).padStart(2, "0");
const secondFormat = String(remainingSeconds).padStart(2, "0");
// Returns the HH:MM:SS format
return `${hourFormat}:${minuteFormat}:${secondFormat}`;
}
/**
* Sums hours based on a criterion.
*
* @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
* @param {number[]} criteria_range - Set of criteria corresponding to the time intervals.
* @param {number} criterion - The criterion for which hours should be summed.
* @returns {string} Sum of the passed durations, or an error message.
*/
function sumHoursIf(sum_range, criteria_range, criterion) {
if (!allAreArrays(sum_range, criteria_range))
return "Pass the intervals for the calculation!";
if (!allArraysHaveSameLength(sum_range, criteria_range))
return "Intervals must be the same size";
// Filters the time intervals for the specific criterion
const hoursToSum = sum_range.filter(
(row, index) =>
String(criteria_range[index]).trim() == String(criterion).trim()
);
// Converts the filtered hours to seconds
const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));
// Sums all the seconds
const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
return accumulator + currentValue;
}, 0);
// Converts the total seconds back to HH:MM:SS format
return convertSecondsToHMS(sumOfSeconds);
}
/**
* Sums hours based on criteria.
*
* @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
* @param {number[]} criteria_range1 - First set of criteria corresponding to the time intervals.
* @param {number} criterion1 - The first criterion for which hours should be summed.
* @param {string[]} criteria_range2 - Second set of criteria corresponding to the time intervals.
* @param {string} criterion2 - The second criterion for which hours should be summed.
* @returns {string} Sum of the passed durations, or an error message.
*/
function sumHoursIf2(
sum_range,
criteria_range1,
criterion1,
criteria_range2,
criterion2
) {
if (
!allAreArrays(
sum_range,
criteria_range1,
criteria_range2
)
)
return "Pass the intervals for the calculation!";
if (
!allArraysHaveSameLength(
sum_range,
criteria_range1,
criteria_range2
)
)
return "Intervals must be the same size";
// Filters the time intervals for the passed criteria
const hoursToSum = sum_range.filter(
(row, index) =>
String(criteria_range1[index]) == String(criterion1).trim() &&
String(criteria_range2[index]).trim() === String(criterion2).trim()
);
// Converts the filtered hours to seconds
const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));
// Sums all the seconds
const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
return accumulator + currentValue;
}, 0);
// Converts the total seconds back to HH:MM:SS format
return convertSecondsToHMS(sumOfSeconds);
}
The criteria can be text or numbers, but the hours must be formatted as "Plain Text". Go to the app script and paste the script and press "CTRL + S". Done. To use it, it's the same process as a native formula.
Once the formula is applied, we can treat it again as a type the program understands by using VALUE; your code should look like this:
=VALUE(sumHoursIf2($C$2:$C$11;$D$2:$D$11;C$14;$E$2:$E$11;$B15))
If everything worked out, this should be your result:
| Categoria | Filme | Série |
|---|---|---|
| Assistido | 5:19:00 | 0:50:00 |
| Assistindo | 0:00:00 | 2:25:01 |
| Quero assistir | 3:00:00 | 2:05:00 |
Just a tip, I hope you liked it, and if you have suggestions, leave them in the comments. Cheers.
Original post: https://dev.to/casewinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p
以上是Google Sheets：如何花费数小时构建 SUMIFS的详细内容。更多信息请关注PHP中文网其他相关文章！
