SUMIFS usando “maior que ou igual a” com valores de texto

Question

Usando o exemplo simplificado a seguir, quero calcular a soma onde Categoria="Graduação" e Data > = 1986-05-01.

Category    Date      Count
Graduation  1985-06-15  1
Graduation  1985-09-12  2
Graduation  1986-05-21  3
Graduation  1986-06-06  4
Graduation  1986-07-03  7
Transfer    1986-08-14  3
Graduation  1986-08-20  1

As datas são todas de texto, ou seja, NÃO são datas do Excel. Estando no formato aaaa-mm-dd, as datas seguem uma ordem adequada.

Aqui estão várias fórmulas que usei e seu resultado, levando à minha pergunta real no final:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"='1986-05-21'")     0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"=1986-05-21")       3   Correct
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"1986-05-21")        3   Correct

Portanto, para a igualdade, você não coloca aspas no valor. Tudo bem.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")       0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'")     12  Correct

Portanto, para maior que, você faz precisa colocar aspas em torno do valor.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'")    12  Incorrect: should be 15

Parece que > = é tratado como se fosse >.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<'1986-05-21'")     6   Incorrect: should be 3
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<='1986-05-21'")        6   Correct

Parece que < é tratado como se fosse < = (isto é, o contrário do caso > =).

Então, você pode me ajudar a ajustar a sintaxe para que o SUMIFS receba greater than e less than correto com valores de texto?

    

Answer 1

A família de funções COUNTIF (S) / SUMIF (S) geralmente tenta interpretar seus dados como numéricos sempre que possível, embora haja, evidentemente, alguma confusão, pois não parece que ela possa consistentemente interpretar os valores de seu intervalo e seu critério como sendo do mesmo formato.

Neste caso:

= SUMIFS (C2: C8, A2: A8, "Graduao", B2: B8, "> 1986-05-21")

O Excel reconhece suas entradas em B2: B8, embora formatadas como texto, como valores de data potencial (ou seja, numéricos).

No entanto, ao mesmo tempo, ele (inútil) interpreta seu critério - "1986-05-21" - como um valor de texto, e assim, uma vez que o Excel não considera nenhum valor numérico (que é precisamente o que as datas estão no Excel - 21/05/1986 é 31553) para ser "maior" do que qualquer valor de texto (digite, por exemplo, = 1000000 > "1" em uma célula em algum lugar - a resposta é FALSE), a resposta é zero.

Quando você adiciona apóstrofos:

= SUMIFS (C2: C8, A2: A8, "Graduao", B2: B8, "> 1986-05-21 '")

O Excel interpreta o seu critério e as entradas em B2: B8 como texto, assim como uma comparação direta entre texto e texto.

O motivo pelo qual você obtém 12 e não 15 para:

= SUMIFS (C2: C8, A2: A8, "Graduao", B2: B8, "> = '1986-05-21'")

é que a entrada em B4 é 1986-05-21 (sem um apóstrofo: um pode aparecer na barra de fórmulas, embora isso não seja tecnicamente parte da cadeia, simplesmente a maneira do Excel de indicar que o valor da célula é texto) e o critério é '1986-05-21' (com apóstrofos "genuínos"). E você pode facilmente testar em uma célula em algum lugar que:

="1986-05-21" >="'1986-05-21'"

é considerado pelo Excel como FALSE (suponho que o Excel interprete a apóstrofe líder no segundo como significando que é "maior" que o anterior - não sei os meandros dessas interpretações: estranhamente,="/ a" > "a" retorna FALSE, enquanto="'a" > "a" retorna VERDADEIRO.

Tudo isso talvez seja uma boa razão para mudar para o SUMPRODUCT, que não parece sofrer essas ambiguidades peculiares:

= SUMPRODUCT ((A2: A8="Graduação") * (B2: B8 >="1986-05-21") * C2: C8)

Atenciosamente