Como criar uma fórmula do Excel SUMIF que possa comparar diferentes formatos de data

Question

Para meus dados de origem, tenho uma coluna de datas no formato dd / mm / dddd e uma coluna de valores ao lado dele.

por exemplo

03/02/2014  5 
17/02/2014  4 
03/03/2014  5 
17/03/2014  4

Em uma planilha de resumo, tenho uma linha de células com datas no formato mmm-yy.

por exemplo

Apr-11        May-11        Jun-11

Na célula abaixo de cada uma dessas datas, preciso inserir a soma de todos os valores de fontes em que o mês corresponde ao cabeçalho.

Eu sei que posso comparar os dois formatos de data usando:

MONTH(A1)=MONTH(D4)

E se as datas coincidissem, eu poderia usar SUMIF assim:

=SUMIF(A1:A53, =D4, B1:B53)

E eu acredito que o Excel 2007+ tem SUMIFS que pode me ajudar, mas infelizmente eu sou obrigado a fazer isso no Excel 2003.

Mas não consigo descobrir como obter o trabalho de comparação do mês dentro da função SUMIF.

Se isso for possível?

    

Answer 1

Não consigo encontrar uma maneira de fazer isso em menos etapas, então ...

Suposições
Estou assumindo que na folha traseira, onde vemos apenas mês-ano, que a data ainda é armazenada como uma data e, em seguida, formatada como um mês (portanto, abaixo dela, na verdade, diz 01/01/2014, em vez de 01- 2014.

Eu também estou assumindo que sua data principal está na coluna A

Os problemas
A questão principal é que você precisa ter suas datas principais em um formato confiável (como em todas as datas, digamos, em maio de 2014, para a mesma data), para que você possa compará-las com precisão.

A primeira instrução de uma fórmula SUMIF requer um intervalo e não é possível executar o tipo de transformações de data necessárias em um intervalo. Você pode fazer isso com uma fórmula de matriz, mas não tive sorte com isso, e fórmulas de matriz tão esotéricas que dificultarão o entendimento de outros usuários.

Em segundo lugar, você está usando MONTH para tornar os dois valores comparáveis. Isso funcionará, mas acabará resumindo qualquer instância do mês, de qualquer ano. Você precisa incluir o ano para garantir que o resultado seja preciso.

A solução mais simples
Eu nunca sou um fã de soluções que dependem de "colunas auxiliares", mas por enquanto, pelo menos, é necessário.

Crie uma coluna ao lado de sua coluna de data principal (esta é sua coluna auxiliar, e você pode ocultá-la ou agrupar ocultá-la para uso normal) e, na célula da primeira linha, adicione esta fórmula:

=EOMONTH(A2,0)

EOMONTH (Fim do mês) retornará o último dia do mês para a data desejada. E o número depois permite que você encontre o último dia de outros meses (coloque -1 lá e ele encontrará o último dia do mês anterior)

Copie esta fórmula e vá para onde você deseja que o SUMIF seja enviado. Na célula, digite isto:

=SUMIF($B2:$B10,EOMONTH($D$1,0),$C2:$C10)

Onde $B2:$B10 é o intervalo da coluna auxiliar, $D$1 é o valor do mês e $C2:$C10 é o intervalo dos valores a serem somados.

Agora repita conforme necessário.

EDITAR
Se você não puder usar o EOMONTH por não poder usar os Add On Packs necessários, poderá usar essa fórmula.

=MONTH($A2)&YEAR($A2)

=SUMIF($B2:$B10,MONTH($D$1)&YEAR($D$1),$C2:$C10)