Сложные критерии в SUMIF

Question

Есть ли способ, как использовать "сложные" критерии в функции SUMIF не используя вспомогательные вычисления?

Я хотел бы суммировать расходы за месяц, имея следующий (автоматически сгенерированный) вход:

  |      A          B
--+----------------------
1 |   2016-10-03    111
2 |   2016-10-12    2300
3 |   2016-11-05    500
4 |   2016-11-17    1000

Итак, ищем формулы, возможно, использующие SUMIF , для построения следующего отчета (год $C$1 и месяц $D1 - это входные данные, которые должны находиться в отдельных ячейках и использоваться для расчета):

   |    C      D        E
 --+-----------------------
   |                 +----+
 1 |   2016    1     |0   |
 2 |           2     |0   |
   |   ...           |    |
10 |           10    |2411|
11 |           11    |1500|
12 |           12    |0   |
                     +----+

Например: ожидаемая формула для E1, как =SUMIF(A1:A4; And(Year(???) = $C$1; Month(???) = $D1); D1:12)

Я могу рассчитать предикаты Месяц / Год для всех входных строк и основать на нем SUMIF , но для этого требуется другой уровень обслуживания, который нежелателен.

Я немного экспериментировал и искал справку, но похоже, что критерии более текстовые, чем дружественные к формуле / функции, есть идеи?

Answer 1

Я не знаю, поможет ли это вашей ситуации. Моя проблема была в том, что у меня есть лист с выплатами. У него есть дата в столбце A и стоимость в столбце B. Я хотел знать, сколько было потрачено каждый месяц. Я изложил это так же, как ваша проблема, чтобы показать, как я преодолел это.

   |    A            B            C
------------------------------------------
1  | 2016/10/03      111    2016/10/01
2  | 2016/10/12      2300   2016/11/01
3  | 2016/11/05      500    2016/12/01
4  | 2016/11/17      1000   2017/01/01

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

Я помещаю дату первого нужного месяца в неиспользуемую ячейку (C1), затем помещаю первое число следующего месяца в ячейку под ней (C2) и так далее.

   |    A            B            C
------------------------------------------
1  | 2016/10/03      111    2016/10/01
2  | 2016/10/12      2300   2016/11/01
3  | 2016/11/05      500    2016/12/01
4  | 2016/11/17      1000   2017/01/01

тогда моя формула для моего первого месяца.

= SUMIFS(B1:B4, A1:A4, "> =" & C1, A1:A4, "<" и C2)

и моя формула на следующий месяц

= SUMIFS(B1:B4, A1:A4, "> =" и C2, A1:A4, "<" и C3)

SUMIFS() Как я вижу, суммирует содержимое ячеек в диапазоне ячеек на основе более чем одного условия, которое должно быть выполнено.

Первая часть формулы B1:B4 - это диапазон ячеек, содержащих затраты, которые необходимо суммировать, если выполняются все условия.

Вторая часть формулы A1:A4 - это диапазон, содержащий даты затрат, относящихся ко второму критерию, который должен быть выполнен.

Третья часть формулы, «> =» & C1, является первым критерием, который должен быть выполнен (дата больше или равна дате в C1).

Четвертая часть формулы, A1:A4, представляет собой диапазон, содержащий даты затрат, относящихся ко второму критерию, который должен быть выполнен (который является тем же диапазоном дат, что и первое условие, которое должно быть выполнено).

Пятая и последняя часть формулы «<» и C2 - это второй критерий, который должен быть соблюден (дата ниже, чем дата в C2).

Если у кого-то есть лучший способ, пожалуйста, сообщите нам