A maneira mais fácil de fazer isso é trapacear e usar uma fórmula mista absoluta / relativa. Esta é uma fórmula de matriz (insira com CTRL + SHIFT + ENTER) inserida na célula B4
, mas ela pode ir a qualquer lugar na linha 4. Ela retornará o número da linha da marcada S
.
=MAX(IF($A$1:A4="S",ROW($A$1:A4)))
Quando copiado para baixo, a segunda parte da referência B4 and A4
aumentará. Isso garante que você obtenha a linha com a maior correspondência acima da linha atual. Você pode inserir essas fórmulas mais rapidamente usando F4 depois de digitar / selecionar o intervalo relevante. Isso fará com que os cifrões percam todas as opções.
Imagem de intervalos
Usadoparasubstituirsuasfórmulas
Depoisdeleraquestãoumpouco(ecombasenaediçãode@SteveTaylor),parecequeoseuusoparaissoéatualizarsuasfórmulas.VocêpodeusaralinharetornadaacimacomINDEX
paraobterintervalosdedadosparasomar.Euvejo2fórmulasquepodemsersubstituídas:
- Cálculototalparacadalinharotuladadedados.Nessecaso,alinhadosubtotalacimapodeserreferenciadadinamicamente.
- Calculartotalparaalinhadosubestado.Nessecaso,osvaloresparasomaacimapodemserreferenciadosdinamicamente.
Paraosdadosdelinhaúnica,vocêpodeusarafórmula,começandoemF3
comoumafórmuladematriz.NotequemudeiparaoSUMPRODUCT
,oquetornamuitomaisfáciliramaisde2colunas.
=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)
Para a fórmula de linha total, você pode usar, começando em F11
, novamente fórmula de matriz:
=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))
Se você quiser uma fórmula para governá-los todos! então você pode combiná-los em um IF
aninhado com base no valor da coluna A
. Aqui está a fórmula de matriz, começando em F2
, que pode ser copiado.
=IF(
A2="S",
SUM(D2:E2),
IF(A2="T",
SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))),
C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))
Esta fórmula não diferencia entre uma linha em branco e uma linha de "dados". Atualmente, retorna 0 para a linha do espaçador, o que é bom.
Imagem de resultados de e fórmulas para dois blocos de seus dados.