Суммирование сумм, если одна строка содержит ключ на другом листе

Question

У меня есть два листа в одном документе LibreOffice Calc:

Лист1:

| Key | Amount  |
|-----|---------|
| ABC |    1    |
| DEF |    2    |
| GHI |    3    |

Лист2:

|  Keys |
|-------|
|  ABC  |
|  XYZ  |
|  JKL  |
|  GHI  |

Теперь я хотел бы добавить суммы в строках Sheet1, но только там, где ключ содержится где-нибудь в Sheet2. Для приведенного выше примера сумма будет равна 4 (ключи ABC с суммой 1 и GHI с суммой 3).

Я решил эту проблему с помощью "Расширенного фильтра" и SUBTOTAL но мне бы очень хотелось решение, в котором мне не нужно повторно применять фильтр при смене листов. Любая помощь будет принята с благодарностью!

Answer 1

Одним из подходов было бы объединить SUMPRODUCT с COUNTIF. Я скопировал твой пример.

Лист2:

Лист1:

Формула в Е2:

=SUMPRODUCT(COUNTIF(Sheet2!$A$2:$A$5,"="&$A$2:$A$4)*$B$2:$B$4)

SUMPRODUCT выполняет вычисление в стиле массива по результату COUNTIF для каждой строки в Sheet1, умноженной на сумму в столбце B. Если предположить, что ключи на Sheet2 не содержат дубликатов, счет будет равен 1 если Sheet2 содержит ключ, или 0 если нет.

Я рекомендую использовать явные диапазоны, а не простые ссылки на столбцы (т. Е. A:A). Вы можете дополнить явные диапазоны пустыми строками, чтобы учесть любое потенциальное расширение, которое вам может понадобиться, и формула все равно будет работать. Тем не менее, Calc требует вечности, чтобы оценить все столбцы.