Столбец суммы до совпадения критериев в соседнем столбце

Question

У меня есть таблица, похожая на следующую:

Col1 | Col2
-----+-----
   A | 12
   A | 4
   B | 5
   A | 16
   A | 8
   B | 91
   C | 6
   A | 55
-----+-----
   C | ???

Что мне нужно, если я наберу C (текстовое значение) в нижней левой ячейке, чтобы отобразить в ??? Отмеченная ячейка - сумма значений Col2 которые находятся выше последнего C (т.е. 12+4+5+16+8+91). Таким же образом, если я наберу B в той же ячейке, я хочу получить 12+4+5+16+8 в результате.

Я попытался с SUMIFS и VLOOKUP без удачи. Как ты это делаешь?

Answer 1

Вы можете попробовать следующую формулу:

=SUM(B1:INDEX(B:B,MATCH(2,IF($A$10=A1:A8,1))-1))

Вводится как формула массива с помощью Ctrl+Shift+Enter.

IF($A$10=A1:A8,1) возвращает массив 1 и false . Если диапазон равен C, то мы получаем массив {false,false,false,false,false,false,1,false} и MATCH(2, {false,false,false,false,false,false,1,false}) возвращает 7 , которое становится 6 когда 1 снимается с него.

Затем индекс возвращает ячейку B6, которую SUM выбрал для создания диапазона B1:B6 .

Если C изменяется на A , мы получаем: {1,1,false,1,1,false,false,1} , что соответствует MATCH 8 и SUM становится B1:B7 .

Answer 2

Использование ссылки на ячейку с текстом немного отличается. Вы должны заключить ссылку в & чтобы Excel использовал ее.

Формула ниже вводится в B9 и читает ячейку A9 как значение, которое нужно исключить. Как видите, вы можете просто использовать =SUMIFS(B1:B8,A1:A8,"<>C") чтобы получить то, что вы хотите, но он не будет читать значение в ячейке A9 . Ниже используется текст.

=SUMIFS(B1:B8,A1:A8,"<>"&A9&"")