Função Excel SUMIFS com critérios complexos

Question

Eu tenho a fórmula SUMIFS, que não funciona, porque o Criteria2 é uma fórmula, não uma condição:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$A$3:$A$10000;IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0))

Sum_range (números a serem somados): Income!$C$3:$C$10000

Criteria_range1 (nome que precisa corresponder): Income!$D$3:$D$10000

Critério1 (nome especificado na primeira planilha em que a fórmula é): C$2

Criteria_range2 (datas): Income!$A$3:$A$10000

Critério2: IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)

O critério 2 é aquele ano e mês a partir da data especificada na célula A3 (primeira folha) são os mesmos do ano e mês da folha de Renda Células da coluna.

É possível incluir condições tão complicadas como critérios, ou a única maneira é calcular ano e mês antes e depois usar intervalos com valores de ano e mês para a função?

    

Answer 1

Não, isso não funcionará, porque Criteria_range2 não contém o mesmo tipo de dados que você está tentando combinar com o Criteria2.

Por exemplo, você pode ter uma data de 14/12/2016 em Criteria_range2 e está tentando obter uma correspondência exatamente com a mesma data inserida na célula A3. Criteria2 irá avaliar como:

IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)
= IF(AND(2016=2016,12=12),Income,0)
= IF(TRUE,Income,0)
= Income

Portanto, seu Criteria2 neste exemplo é avaliado como Renda! C $ 3 e não corresponde às datas que estão em Criteria_range2.

Se a data não tivesse correspondido, Criteria2 seria 0, o que também não corresponderia a nenhum dado em Criteria_range2.

Mas, mesmo se alterarmos a instrução IF para retornar uma data, essa data nem sempre corresponderá às datas Criteria_range2. Por exemplo, se alterarmos a função Criteria2 para:

date(year(A3),month(A3))

então a função não funcionaria. Precisamos inserir um dia também para data () para avaliar. Mas adicionar um dia, ou simplesmente usar A3, significa que Criteria2 só avaliará como verdadeiro quando a data exata coincidir, não verificando apenas mês e ano como você deseja.

Calcular ano e mês nas colunas primeiro e usar um critério para verificar cada um individualmente funcionará.

Ou, se você quiser ser desnecessariamente extravagante, adicione uma única coluna em Renda! E3: E10000 e preencha com a fórmula

=YEAR(A3)&MONTH(A3)

Em seguida, você pode criar seus SUMFIFS:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$E$3:$E$10000;YEAR(A3)&MONTH(A3))

As fórmulas funcionam como critérios SUMIFS, mas precisam ser avaliadas para corresponder aos dados contidos nos intervalos.