Como usar subtotal com if no excel?

ESTA FÓRMULA É PURA MÁGICA.

Se você deseja escrever uma fórmula SUBTOTAL no Excel com IF (condição), você precisa usar várias funções para fazer isso. Mas antes de fazermos isso, vamos entender os dados que temos para este exemplo.

No exemplo acima, você tem três colunas:

• Nome
• Laje de idade
• Gênero

E quando você filtra uma laje da coluna Laje de Idade, ela exibe o número de mulheres na célula F1. Então isso significa que temos uma fórmula que exibe o número de valores filtrados, mas com uma condição.

A fórmula que temos:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Compreendendo a fórmula SUBTOTAL IF

Esta fórmula usa cinco funções: SUMPRODUCT, SUBTOTAL, OFFSET, ROW e MAX. Portanto, para compreender esta fórmula, precisamos dividi-la em várias partes.

1.LINHA(C2:C41)-2.0)

Esta parte da fórmula usa as funções MIN e ROW.

• No ROW, referenciamos as colunas “Gênero” e ela retorna uma matriz de números de linha.
• Depois disso, MIN pega esse array ou números de linha e retorna o número mínimo de linha. É por isso que temos 2 nesta parte da fórmula.

2.LINHA(C2:C41)

Nesta parte, temos apenas a função ROW, que retorna um array de números de linhas.

3. DESLOCAMENTO(C2,LINHA(C2:C41)-MIN(LINHA(C2:C41)),0)

Agora temos a função OFFSET. Ajuda a criar uma referência a um intervalo usando uma referência de célula como ponto de partida. No argumento de referência , nos referimos à célula C2, a primeira célula a partir da qual começa nossa faixa de gênero.

No argumento das linhas temos esta parte da fórmula discutida acima nas duas primeiras partes. Depois disso, no argumento cols, usamos 0. Com tudo isso, OFFSET retorna um array com todos os valores da coluna “Sexo”.

4. SUBTOTAL(3,OFFSET(C2,LINHA(C2:C41)-MIN(LINHA(C2:C41)),0))

Usamos o array retornado pelo OFFSET no SUBTOTAL. E no function_num usamos 3, que diz ao SUBTOTAL para usar a função COUNTA para cálculo.

Quando você usa um filtro na coluna “Age Slab”, esta parte SUBTOTAL da fórmula retorna uma matriz contendo 0 e 1.

Nesta tabela temos 1 para valores equivalentes ao valor para o qual aplicamos o filtro. Veja o exemplo abaixo:

5. (C2:C41=E1)

Esta parte da fórmula retorna uma matriz testando uma condição. Nesta condição, testamos se o valor do intervalo é “Female”, e retorna TRUE e FALSE no array.

Nesta tabela temos TRUE para o valor “Female” e FALSE para os demais.

7. Última parte

No final, temos duas tabelas em SUMPRODUCT. E também temos um operador asterisco entre essas matrizes.

Quando multiplicamos as duas tabelas entre si, temos uma única tabela com 0 e 1. Nesta tabela, um (1) é para o valor “Feminino” em gênero e 21-30 para “Laje de Idade”.

No final, SUMPRODUCT retorna a soma usando este array. E essa soma é o número de células com o valor “Feminino” na coluna de gênero quando você filtra de 21 a 30 anos na coluna “Faixa etária”.