Критерии ITSUMIF для ячеек, не соответствующих условию

Question

У меня есть таблица Excel, которая выглядит следующим образом:

# |A           |B
-----------------------------
1 |Expenses    |Category
2 |$5          |Food
3 |$10         |Entertainment
4 |$10         |Haircut

Я хочу создать раздел, в котором перечислены суммы для каждой категории. Что-то, что организовано так:

# |H            |I
-----------------------------
1 |Category     |Total Expenses
2 |Food         |
3 |Entertainment|
4 |Other        |

Подводить итоги по категориям "Еда и развлечения" было довольно легко. Я использовал функцию SUMIF, чтобы проверить диапазон столбца B "Категория" по отношению к ячейке в столбце H "Категория", которая суммирует значения, если они совпадают. Например, у "I2" была формула, подобная этой:

=SUMIF(B$2:B$999, H2, A$2:A$99)

Тем не менее, мне очень трудно настроить категорию "Другое". Я хочу, чтобы он отображал любые значения, которые имеют категорию, которая не отображается в столбце H (например, "Стрижка"). Я пытался сделать следующее:

=SUMIF(B$2:B$999, NOT(OR(H2,H3)), A$2:A$99)

Но это, кажется, не захватывает клетки. В конечном счете, я хочу, чтобы раздел критериев SUMIF захватывал ячейки, которые не содержат значений, найденных в ряде других ячеек.

РЕДАКТИРОВАТЬ: Это для электронной таблицы, которая предназначена для клиента, который не очень требователен к компьютеру, поэтому в идеале любое решение должно быть таким, которое накладывает очень мало нагрузки на конечного пользователя (например, не требует от них понимания того, что макросы в порядке). Также полная таблица содержит столбец с датой, поэтому сортировка по категориям не будет работать.

Answer 1

Что вам нужно, это Pivot Table .

Выберите одну из ячеек своего стола, скажем, A1 . Создайте сводную таблицу, нажав: Вставить> Сводная таблица. Нажмите ОК

На этом этапе у вас будет Pivot Table созданная на новом листе. Чтобы вычислить то, что вы хотите, перетащите Category из списка полей, в поле с правой стороны таблицы, к Row Labels , затем перетащите Expenses в ∑ Values .

РЕДАКТИРОВАТЬ

Как вы задали в своем вопросе, Pivot Table не нужно создавать на отдельном листе. Вместо нажатия кнопки «ОК», когда в сводной таблице появится диалоговое окно, вы можете указать, где вы хотите создать сводную таблицу.

Кроме того, не забывайте обновлять таблицу каждый раз, когда ее содержимое изменяется, щелкнув правой кнопкой мыши сводную таблицу и нажав кнопку Обновить.

РЕДАКТИРОВАТЬ 2

Извините, я неправильно прочитал ваш вопрос, вы смогли вычислить сумму категорий, поэтому вам не нужна сводная таблица. То, что вам нужно, это суммировать другие категории, я предлагаю:

Вместо того, чтобы пытаться суммировать все категории, которые не были суммированы, почему бы вам не сложить итоговое значение из A1:A999 и вычесть все суммированные значения в столбце I?

Давайте предположим, что ваше Other поле находится на H4:

= СУММА (1 $:999 $) - СУММА (I $ 2:I $ 3)

Answer 2

SUMIFS позволяет вам указать более одного условия (в Excel 2007 или более поздней версии), чтобы вы могли использовать эту формулу

=SUMIFS(A:A,B:B,"<>"&H2,B:B,"<>"&H3)

Это будет суммировать столбец A, где значение соответствующего столбца B не равно H2 И не равно H3

Если у вас есть более длинный список элементов, которые нужно исключить, то этот путь приведет к длинной формуле, поэтому другой способ исключить диапазон будет использовать SUMPRODUCT как это

=SUMPRODUCT(ISNA(MATCH(B2:B100,H2:H5,0))+0,A2:A100)

Это будет суммировать A2:A100, когда значение соответствующего столбца B не совпадает ни с одним из значений в H2:H5

Answer 3

Простой способ сделать это - отсортировать данные по категориям и использовать SUBTOTAL. Вы можете использовать функцию SUBTOTAL несколько раз или (более простой способ) выделить отсортированные столбцы A и B и использовать промежуточный итог из структуры на ленте данных (я забыл, в каком меню он находился в более ранних версиях Excel). Он будет автоматически вставлять промежуточные итоги при каждом изменении категории, включать общую сумму и настраивать обработку контуров, чтобы вы могли свернуть или развернуть промежуточные итоги.

Промежуточный итог очень удобен для такого рода вещей. Он поддерживает несколько уровней итогов, поэтому вы можете иметь отдельные промежуточные итоги по отделам, по категориям в отделе и т.д.

Answer 4

Как насчет того, чтобы установить для I4 =SUM(A$2:A$99) - SUM(I$2:I3) - все данные в столбце B за вычетом (под) итогов, которые вы уже накопили в столбце I  Если вам нужно иметь возможность добавлять (и / или удалять) категории с минимальными суетой и беспокойством, посмотрите на

=SUM(A$2:A$99) - SUM(OFFSET(I$2,0,0,ROW()-2,1))

где функция ROW() определяет, в какой строке она находится (то есть в какой строке находится строка «Other»).  Вычтите 2 для строки 1 и текущей строки; например, если у вас есть восемь суммированных категорий, в ячейках H2:H9 , то Other находится в H10 , и вы хотите суммировать I2:I9 , что составляет восемь (10-2) строк.