Excel: soma valores em várias colunas dentro de cada linha se os critérios em outra coluna (também múltiplos) forem atendidos [closed]

Question

Por favor, consulte a imagem anexada contendo uma tabela de exemplo que é semelhante à pasta de trabalho real:

EuqueroescreverfórmulasnascolunasB-D,demodoque,paracadacliente,asclassificaçõessejamadicionadascombasenotipo.Porexemplo,paraoclienteAva,queroqueasfórmulasproduzamisso:22nacolunaB(TipoA),poiselatemtodooTipoAsemsualinhacomclassificações7,8e7(7mais8mais7éiguala22).Seumtiponãoforencontradonalinha(nesteexemploTipoBeC),osresultadosnascolunasCeD(TipoBeC,respectivamente)serão0.Ascolunas"Tipo, Nome, Código e Classificações" serão estender para a direita (para cobrir mensalmente) e ter outras colunas entre eles.

Eu tentei ifs, vlookups, sumifs, correspondência de índice, etc. aninhados, mas não encontrei o mix de fórmulas certo para eles serem praticáveis no longo prazo, desde novos grupos de colunas para o "Tipo, Nome, Código e as classificações "são adicionadas constantemente. As fórmulas que criei precisavam ser atualizadas sempre que novos grupos dessas colunas fossem adicionados.

    

Answer 1

Fórmula para a célula B2

=SUM((($E2:$XFA2)=B$1)*IFERROR(VALUE($H2:$XFD2),0))

Esta é realmente a "fórmula de matriz" (precisa ser confirmada por CTRL + SHIFT + ENTER) Após a confirmação, estique essa célula além das colunas B, C, D.

Esta fórmula encontra a coluna "rating" sempre 3 colunas à direita da coluna "Tipo".

    

Answer 2

Isso faz o que você quer e escala! está em VBa embora. Eu sei que você pediu uma função de planilha, mas como você não (não) teve uma resposta adequada, estou oferecendo isso.

Lembre-se de que não há como desfazer, por isso, primeiro faça um backup do seu arquivo.

Option Explicit    
Sub WalkThePlank()

Dim startRow As Integer
startRow = 2                     'update this if needed Cap'ain, assumes content starts on row 2 as your "headings" be on row 1. Argggghhhh

Dim startCol As Integer
startCol = 69                   '69 is for Column E, the first column you want to look at. If this be wrong, then I'll feed myself to the sharks

Dim currentCol As Integer    
Dim typeToUse As String        
Do While Range("A" & startRow).Value <> ""
currentCol = startCol    
Do While Range(Chr(currentCol) & 1).Value <> ""
    Dim heading As String
    heading = Range(Chr(currentCol) & 1).Value        
    If (LCase(heading) = "type") Then
        typeToUse = Range(Chr(currentCol) & startRow).Value
    End If        
    If (LCase(heading) = "ranking") Then
        If LCase(typeToUse) = "a" Then
            Range("B" & startRow).Value = Range("B" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "b" Then
            Range("C" & startRow).Value = Range("C" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "c" Then
            Range("D" & startRow).Value = Range("D" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
    End If
    currentCol = currentCol + 1
Loop    
startRow = startRow + 1
Loop    
End Sub

Como eu adiciono o VBA no MS Office?

O único critério é, da esquerda para a direita, a coluna Tipo deve vir antes do Ranking.

Na sua captura de tela, você tem "grupos" de 4 colunas

Type Name Code Rating

Com o acima, você pode ter um grupo de 2 ou 20! Ainda vai funcionar

Se você adicionar um grupo adicional de colunas à extrema direita de seus dados, seja um grupo de 2, 3, 4 (como você possui) ou qualquer outro número, ele ainda funcionará

Antes

DepoisdeexecutaroVBa

Emseguida,excluaosvaloresnacolunaTypeA,TypeBeTypeCeadicionemais2colunas(omitiacolunachamadaArghparamostrarcomoaestruturadiferentenãoimporta)

EexecuteexatamenteomesmoVBa