Somar várias linhas em uma única condição

Question

Suponha que eu tenha a seguinte tabela na planilhamês:

E a seguinte tabela em planilha__dados:

Na planilha__dadosGostaria de somar todos os valores das colunas P, R, T, V e X, para o nome da categoria fornecida.

Para uma única linha, sei que posso usar a seguinte fórmula: (cat6 por exemplo)

=IF(
    SUMIF(month!$O$5:$O$34;__data!$A7;month!$P$5:$P$34)<>0;
    SUMIF(month!$O$5:$O$34;__data!$A7;month!$P$5:$P$34);
    "")

Agora, acredito que isso deveria ser possível com SUMPRODUCT. Mas não consigo entender como isso deve ser codificado.

Eu tenho esta fórmula SUMPRODUCT, mas isso resulta em #VALUE!erro;

=SUMPRODUCT(
    --(month!$O$5:month!$O$34=__data!$A7);
    (
        month!$P$5:month!$P$34 +
        month!$R$5:month!$R$34 + 
        month!$T$5:month!$T$34 + 
        month!$V$5:month!$V$34 + 
        month!$X$5:month!$X$34)
)

Acredito que isso tenha algo a ver com a forma como os valores em P,R,T,V e X são preenchidos. Isso é feito com a seguinte fórmula;

=IF(SUMIF($B$5:$B$123;O8;$D$5:$D$123)<>0;SUMIF($B$5:$B$123;O8;$D$5:$D$123);"")

Answer 1

Você pode fazer isso facilmente com uma fórmula. Mas você precisa ter um método para retornar colunas descontínuas.

• Nomeei a tabela de dados catTblpara facilitar a consulta.
• Encontre a linha usando a MATCHfunção:
  • =MATCH(A2,INDEX(catTbl,0,1),0)
    • A categoria está em A2 e a INDEXfunção retorna todas as linhas da primeira coluna.
  • A N(IF(1,{2,4,6,8,10})) will return the relevant columns to thefunção de construção INDEX`.
  • Depois é só SUMa matriz

Então, toda a fórmula:

=SUM(INDEX(catTbl,MATCH(A2,INDEX(catTbl,0,1),0),N(IF(1,{2,4,6,8,10}))))

Answer 2

Geralmente é difícil trabalhar com fórmulas não contínuas do Excel.

Sugiro adicionar uma linha auxiliar onde você indica quais colunas deseja resumir, então você pode usar esta fórmula relativamente simples:

=SUMIF($Q$4:$Y$4,1, INDEX($Q$6:$Y$19,MATCH(M6,$P$6:$P$19,0),0))