Как объединить несколько аргументов массива - SUMIF(S)? SUMPRODUCT?

Question

Я был бы признателен за вклад в этом.

Date          Value
7-Feb-2016     100
20-Feb-2016    200 
15-Mar-2016    300
25-Mar-2016    400

Мои два критерия для суммирования:

1. Дата должна быть <= 31 марта 2016 г .; и если 1. встречается, то

2. дата минус 31 марта 2016 года <= 30

Другими словами, для какой-либо конкретной даты, предшествующей концу месяца (здесь, 31 марта 2016 г.), какова сумма, у которой продолжительность дня составляет менее 30 дней?

Я ожидаю, что на выходе будет 700 (300+400).

Я пробовал это:

SUMIFS(sum range,date range,"<="&42460,date range,date range-42460"<=30")

• обратите внимание, что «42460» читается как 31 марта 2016 года в Excel, и для второго критерия я попытался использовать диапазон дат минус константа.

Можно ли включить оба критерия в формулу одной строки?

Answer 1

Правильная форма SUMIFS():

=SUMIFS($B$2:$B$5, $A$2:$A$5, ">=" & 42460-30, $A$2:$A$5, "<=" & 42460)

То, что вы ищете, это все с 31 марта 2016 года по 31 марта 2016 года - 30 дней.  Итак, 31 Mar 2016 - 30 <= Date <= 31 Mar 2016

Answer 2

• Ваш вопрос не имеет (полностью) смысла.  У вас есть условия

  1. Дата должна быть ≤ 31 марта 2016 года, и
  2. Дата минус 31 марта 2016 ≤ 30

  Ну, а если дата ≤ 31 марта 2016 года, то дата минус 31 марта 2016 года автоматически должна быть ≤ нуля.  Т.е. ваше второе условие автоматически выполняется для каждой даты (строки), которая удовлетворяет первой.

  Из вашего примера (даты в марте включены, даты в феврале исключены), я предполагаю, что вы имеете в виду 31 марта 2016 года минус дата ≤ 30.  Это более практично выражено как дата ≥ 31 марта 2016 года минус 30 (то есть дата ≥ 1 марта 2016 года).  Между прочим, я заметил, что Скотт Крэйнер (безотносительно) исправил это для вас, не объясняя это.

• Не конвертируйте даты в числа, а затем используйте эти числа в формулах.  Это приводит к созданию электронной таблицы, которую вам будет трудно поддерживать, и никто больше не сможет ее понять.  Вы должны либо
  • Поместите дату окончания в ячейку (скажем, E1), а затем выполните

    = SUMIFS ($ B $ 2:$ B $ 5, $ A $ 2:$ A $ 5, "> =" & $ E $ 1-30, $ A $ 2:$ A $ 5, "<=" & $ E $ 1)

    или же
  • Поместите дату в формулу так, чтобы ее могли понять как люди, так и Excel:

    = SUMIFS ($ B $ 2:$ B $ 5, $ A $ 2:$ A $ 5, "> =" & DATE (2016,3,31) -30, $ A $ 2:$ A $ 5, "<=" & DATE (2016,3,31))

  • Вы могли бы также сделать

    = SUMIFS ($ B $ 2:$ B $ 5, $ A $ 2:$ A $ 5, "> = 3/1/2016", $ A $ 2:$ A $ 5, "<= 3/31/2016")

    если вы знаете даты начала и окончания, но я не могу понять, как сделать математику (вычесть 30 дней) в одной строке формулы, используя эту запись.
• Вы, конечно, понимаете, что если E1 - 28 февраля 2015 года, то E1-30 - 29 января 2015 года, верно?  Если вы действительно хотите 30-дневное окно, это нормально для меня.  Но, если вы действительно хотите добавить все записи за определенный месяц, скажем так, потому что есть лучшие способы сделать это.