Somando por correspondência / índice com uma correção e um critério de variante

Question

Eu tenho a seguinte tabela.

    Column A    Column B         Column C  

1  Board Size   Quantity         Classroom      <----- (Column header)
2     240          1           Classroom - 1
3     660          4           Classroom - 1
4     1100         1           Classroom - 1
5     1100         1           Classroom - 2
6     240          4           Classroom - 2
7     660          1           Classroom - 2
8     240          1           Classroom - 3
9     660          3           Classroom - 3
10    1100         2           Classroom - 3

Meu primeiro pedido:
Na tabela abaixo, quero dizer em F2 , por exemplo: Quando E2 corresponde a Column C AND $F$1 corresponde a Column A , forneça o valor da correspondência em Column B .

Meu segundo pedido:
Em G2 , em vez disso, quero ter a soma, quando $E$2 corresponde a Column C AND $G$1 corresponde a Column A .

    Column E         Column F       Column G  

1   Classroom          240            240         <----- (Column header)
2   Classroom - 1       1              6
3   Classroom - 2       4   
4   Classroom - 3       1

Alguém pode me ajudar a fazer isso com MATCH / INDEX, por favor.

    

Answer 1

Em F2:

=SUMIFS(B:B,A:A,$F$1,C:C,$E2)

e copie para baixo

No G2:

=SUMIFS(B:B,A:A,$G$1)

Para usar INDEX / MATCH, ele deve ser uma forma de matriz da fórmula:

Em F2:

=INDEX($B$2:$B$10,MATCH(1,($A$2:$A$10=$F$1)*($C$2:$C$10 = $E2),0))

Sendo uma fórmula de matriz, ela deve ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Outra desvantagem das fórmulas do Array é que não se deve usar referências de colunas completas, pois a fórmula calculará cada célula referenciada, ou seja, mais de 2 milhões de cálculos para cada fórmula.

A referência precisa ser apenas para o conjunto de dados.

A fórmula em G2 permanecerá a mesma que não pode ser feita com um INDEX / MATCH, já que INDEX / MATCH só retornará o primeiro número e não quaisquer outros.