Home > Article > Web Front-end > Google Sheets: SUMIFS for durations (hours), part 2
These days I made a post showing how to create two custom formulas for Google sheets to add hours based on criteria (here). Their problem in my opinion is that they were not flexible. What if I want N criteria? Well, now you can!
Now the formula takes 3 sets of data: the durations, the criteria, and the instructions to filter. Let's go back to the test spreadsheet:
Título | Duração | Categoria | Status | Curti |
---|---|---|---|---|
Perdido em Marte | 01:00:00 | Filme | Já assisti | Sim |
Interestelar | 02:49:00 | Filme | Já assisti | Não |
John Wick | 01:30:00 | Filme | Já assisti | Não |
Vingadores: Ultimato | 03:00:00 | Filme | Quero assistir | |
Stranger Things | 00:45:00 | Série | Assistindo | |
The Witcher | 01:00:01 | Série | Assistindo | |
O Mandaloriano | 00:40:00 | Série | Assistindo | |
Breaking Bad | 00:50:00 | Série | Já assisti | Sim |
A Casa de Papel | 00:55:00 | Série | Quero assistir | |
Game of Thrones | 01:10:00 | Série | Quero assistir |
Let's update our app script, I suggest creating another test spreadsheet and starting a new one, remembering that the times must be in “plain text”. The code is here:
/** * Converte uma string no formato HH:MM:SS para o total em segundos. * * @param {string} hms - A string no formato HH:MM:SS. * @returns {number} O total em segundos. */ function converterHMSParaSegundos(hms) { const partes = String(hms).split(":"); const [horas, minutos, segundos] = partes; return Number(horas) * 3600 + Number(minutos) * 60 + Number(segundos); } /** * Converte um número de segundos para o formato HH:MM:SS. * * @param {number} segundos - O total de segundos. * @returns {string} O tempo no formato HH:MM:SS. */ function converterSegundosParaHMS(segundos) { const horas = Math.floor(segundos / 3600); const minutos = Math.floor((segundos % 3600) / 60); const segundosRestantes = segundos % 60; return `${String(horas).padStart(2, "0")}:${String(minutos).padStart( 2, "0" )}:${String(segundosRestantes).padStart(2, "0")}`; } /** * Filtra as linhas da matriz de dados com base nos critérios fornecidos e retorna as durações correspondentes. * * @param {Array<string>} duracoes - Array de durações em formato de texto. * @param {Array<Array<string>>} dados - Matriz de dados onde cada linha corresponde a um conjunto de valores. * @param {Array<string>} criterios - Array de critérios para filtrar as linhas da matriz de dados. * @returns {Array<string>} Retorna as durações que correspondem aos critérios. */ function filtroDeLinhas(duracoes, dados, criterios) { const linhas_na_matriz = duracoes.length; const matriz_de_criterios_concatenada = []; for (let linha = 0; linha < linhas_na_matriz; linha++) { let incluirLinha = true; for (let coluna = 0; coluna < dados[linha].length; coluna++) { if (!criterios.includes(dados[linha][coluna])) { incluirLinha = false; break; // Sai do loop se algum critério não for atendido } } if (incluirLinha) { matriz_de_criterios_concatenada.push(duracoes[linha]); } } return matriz_de_criterios_concatenada; } /** * Soma as horas filtradas de acordo com os critérios fornecidos. * * @param {Array<string>} duracoes - Array de durações em formato de texto. * @param {Array<Array<string>>} intervalo_de_criterios - Matriz de dados onde cada linha corresponde a um conjunto de valores. * @param {...string} criterios - Um ou mais critérios para filtrar as linhas da matriz de dados. * @returns {string} A soma das durações no formato HH:MM:SS. */ function somarHorasComCriterios( duracoes, intervalo_de_criterios, ...criterios ) { // Verifica se todos os argumentos são arrays if ( !Array.isArray(duracoes) || !Array.isArray(intervalo_de_criterios) || !Array.isArray(criterios) ) { return "Passe os intervalos para o cálculo!"; } const horasParaSomar = filtroDeLinhas( duracoes, intervalo_de_criterios, criterios.flat(Infinity) ); const horasEmSegundos = horasParaSomar.map((n) => converterHMSParaSegundos(n) ); const somaDosSegundos = horasEmSegundos.reduce( (acumulador, valorAtual) => acumulador + valorAtual, 0 ); return converterSegundosParaHMS(somaDosSegundos); }
Let's say I pass:
Film | I've already watched it | No
My sum would be: 04:19:00
And remember that I can wrap this with the native function: =VALUE and format it as “duration” and sheets will work sums again as if using a native formula!
As I said in the last post, leave your comments with suggestions or questions. Hugs.
The above is the detailed content of Google Sheets: SUMIFS for durations (hours), part 2. For more information, please follow other related articles on the PHP Chinese website!