Как сложить значения из столбца, если выполняются два условия? Один — это дата, а другой – ключевое слово
Я пытался найти способ сделать это, но он не работает так, как мне нужно, если это вообще возможно. У меня есть такая таблица:
Мне нужно сложить числа в столбце C, если день в столбце A меньше или равен числу, и мне также нужно складывать числа только в том случае, если слово в столбце B соответствует ключевому слову.
В этом примере день может быть меньше 2 и ключевое слово «е». Я должен получить результат 2, но это не работает.
Я пробовал с =SUMPRODUCT((C1:C3)*(DAY(A1:A3)<=2)*(B1:B3 = "=*e*")) и =IF(DAY(A1:A3)<=2,SUMIF(B1:B3,"=*e*",C1:C3))
Заранее спасибо.
Ответы 2
SUMPRODUCT версия, возможно, вам более знакомая:
=SUMPRODUCT(
(DAY(Table1[Column A])<=2) *
(ISNUMBER(SEARCH("e",Table1[Column B]))) *
(Table1[Column C]))
Или SUMIFS версия
=SUMIFS(C:C, A:A, "< = " & DATE(YEAR(A2), MONTH(A2), 2), B:B, "*e*")
И действительно принести это домой (я не использую Excel365, так что это может быть неправильно, но вы поняли). Если это стоит делать, то стоит делать с LET:
=LET(
rangeA, Table1[Column A],
rangeB, Table1[Column B],
rangeC, Table1[Column C],
dayCondition, LAMBDA(x, IF(DAY(x) <= 2, 1, 0)),
textCondition, LAMBDA(y, IF(ISNUMBER(SEARCH("e", y)), 1, 0)),
filteredSum, LAMBDA(a, b, c, IFERROR(SUMPRODUCT(dayCondition(a) * textCondition(b) * c), 0)),
result, filteredSum(rangeA, rangeB, rangeC),
result
)
Хотелось бы увидеть еще более запутанные применения LET/LAMBDA и тому подобного.
Спасибо большое, первая фурмула заработала так, как мне нужно.
Если все даты относятся к тому же году и месяцу, что и первая дата, то следует предпочесть SUMIFS. Это наиболее совместимо. И критерии уже поддерживают подстановочный знак *.
Пример:
Формула в H2 в переводе:
=SUMIFS(C:C,A:A,"<"&DATE(YEAR(A2),MONTH(A2),F2),B:B,"*"&F3&"*")
Если вы используете Microsoft 365, можно использовать комбинацию SUM и FILTER.
Формула в J2 моего примера переведена:
=SUM(FILTER(C:C,(DAY(N(A:A))<F2)*(ISNUMBER(SEARCH(F3,B:B)))))
Как видите, самым сложным является поиск по содержимому, поскольку FILTER не поддерживает какие-либо подстановочные знаки в критериях. По крайней мере, нет, насколько я знаю. Возможно, появится другой ответ.
Часть ISNUMBER(SEARCH(F3,B:B)) использует SEARCH, которая возвращает позицию первого вхождения искомого слова в строке, а в противном случае #VALUE ошибку, которая не является числом.
Часть N(A:A) используется, чтобы избежать ошибки #VALUE при попытке получить DAY из нечислового значения.
Поскольку в вашем вопросе неясно, должен ли день быть меньше, меньше или равен критерию поиска, мои формулы фильтруют меньше, чем. Замените < на <=, чтобы это изменить.
Все даты относятся к одному и тому же году и одному месяцу?