Функция Excel SUMIFS со сложными критериями

Question

У меня есть формула SUMIFS, которая не работает, потому что Criteria2 - это формула, а не условие:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$A$3:$A$10000;IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0))

Sum_range (цифры для суммирования): Income!$C$3:$C$10000

Criteria_range1 (имя, которое должно соответствовать): Income!$D$3:$D$10000

Критерии1 (указано имя на первом листе, где формула): C$2

Criteria_range2 (даты): Income!$A$3:$A$10000

Критерии2: IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)

Критерием2 является то, что год и месяц с даты, указанной в ячейке A3 (первый лист), совпадают с годом и месяцем из ячеек столбца таблицы доходов A.

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

Answer 1

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

Например, у вас может быть дата 14/12/2016 в Criteria_range2, и вы пытаетесь получить совпадение с точно такой же датой, введенной в ячейку A3. Критерии2 будут оцениваться как:

IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)
= IF(AND(2016=2016,12=12),Income,0)
= IF(TRUE,Income,0)
= Income

Таким образом, ваш критерий2 в этом примере оценивается как доход!C $ 3 и не соответствует датам, указанным в Criteria_range2.

Если дата не совпадает, Criteria2 будет иметь значение 0, что также не будет соответствовать никаким данным в Criteria_range2.

Но даже если мы изменим оператор IF, чтобы он возвращал дату, эта дата не всегда будет соответствовать датам Criteria_range2. Например, если мы изменили функцию Criteria2 на:

date(year(A3),month(A3))

тогда функция не будет работать. Нам нужно также ввести день для date() для оценки. Но добавление дня или простое использование A3 означает, что Criteria2 будет оцениваться как истинный, только когда точная дата совпадает, а не проверять только месяц и год, как вы хотите.

Сначала будет рассчитан год и месяц в столбцах и будет использоваться критерий для проверки каждого из них в отдельности.

Или, если вы хотите быть излишне модным, вы можете добавить один столбец в доход!E3:E10000 и заполните формулой

=YEAR(A3)&MONTH(A3)

Затем вы можете сделать свои SUMFIFS:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$E$3:$E$10000;YEAR(A3)&MONTH(A3))

Формулы работают как критерии SUMIFS, но их необходимо оценивать на соответствие данным, содержащимся в диапазонах.