Google スプレッドシート: 数時間かけて SUMIFS を作成する方法

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?

Creating generic data for testing

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

Script

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);
}

How to use?

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 スプレッドシート: 数時間かけて SUMIFS を作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。