How to add values related to a line, searched with the Index + Corresp function in Excel?

Asked

Viewed 3,906 times

0

I wanted to add some values related to the names given to the cells "Ref 1, 2 and 3" summing all the values of the columns "N1, N2 and N3" according to the "Ref" sought, for this I used this formula: =ÍNDICE(SOMA(C70:E72);CORRESP(F73;B70:B72;0)), but it didn’t work! For it was for the result of the sum of the "Ref 2" that would be "50", in which case it would be the sum of the columns "N1, 2 and 3" of the line "Ref 2".

Any solution to this error??

inserir a descrição da imagem aqui

excel

2 answers

4


According to your spreadsheet, the formula that will be inserted from cell F74 would look like this:

=SOMA(ÍNDICE($B$70:$E$72;CORRESP($F$73;$B$70:$B$72;0);0))

1

Use the formula Soma(Intervalo), along with the Somase(Intervalo,critério,intervalo_soma)

Use like this: =Soma(Somase(B70:B72,B71,C70:C72),Somase(B70:B72,B71,D70:D72),Somase(B70:B72,B71,E70:E72))

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.