Index Match e SUM com vários critérios

Question

Estou tentando obter a soma das linhas com base em uma pesquisa de correspondência de índice da tabela abaixo Tabela mensal

Eu estou olhando para alterar o intervalo de soma na linha com base na data que eu seleciono. Por exemplo, se eu selecionar 16 de outubro em uma célula suspensa, eu obteria o total de 10 meses em 2016 para o código de 6 dígitos fornecido. "> Consol

Estou tentando a fórmula abaixo, que me dá a soma das 10 primeiras células da linha, independentemente da data que eu selecionar.

= IFERROR (SUM (ÍNDICE (INDIRETO ("" & $ C $ 3 & "! C: C"), MATCH ($ A10, INDIRECT ("" & $ C $ 3 & "! B: B "), 0)): INDEX (INDIRECT (" "& $ C $ 3 &"! C: Z "), MATCH ($ A10, INDIRECT (" "& $ C $ 3 &"! B: B " ), 0), MÊS ($ C $ 2))), "")

Aprecie suas sugestões pls

    

Answer 1

Você deseja somar uma parte de uma linha de uma matriz, em que a data é menor ou igual a um valor especificado.

Primeiro, vamos descobrir como obter uma linha da matriz. A função INDEX ()

INDEX(array, row_num, [col_num])

retornará uma linha inteira se o col_num estiver definido como zero. Então essa função

=INDEX(C9:O17,MATCH(403300,B9:B17,0),0))

retorna a linha de seus dados, onde a conta (?) é 403300. Você pode verificar isso, destacando a fórmula na barra de fórmulas e digitando F9. Isso mostrará o valor da fórmula - uma matriz dos dados na linha 403300.

Agora, basta adicionar a parte da linha em que o mês é menor ou igual ao mês especificado. SUMIF() fará isso.

SUMIF(range,criteria,[sum-range])

SUMIF () verifica um intervalo especificado (suas datas) que corresponde a um critério (< = seu mês especificado) e soma as células correspondentes no intervalo de soma (a linha escolhida com a fórmula INDEX () acima). Colocando tudo isso junto, e usando a tabela de dados mocked-up abaixo, esta fórmula

=SUMIF(C7:O7,"<="&$E$4,INDEX(C9:O17,MATCH($D$4,B9:B17,0),0))

em G4 dá a soma da conta em D4 até a data em E4.

Eucoloqueitudoemumaplanilhaesemmenussuspensos,masvocêpodeadicionarfacilmenteessesrecursos.Sevocêrealmenteprecisaespecificaraplanilhacomumalistasuspensa,vocêtemqueusarummontedeINDIRECT(),oqueficaumpoucobagunçado.Euvimcomisso,ondeonomedaplanilhaestáemC4:

=SUMIF(INDIRECT(C4&"!"&"C7:O7"),"<="&E4,INDEX(INDIRECT(C4&"!"&"C9:O17"),MATCH(D4,INDIRECT(C4&"!"&"B9:B17"),0),0))

Espero que isso ajude e boa sorte.