Критерии Google Sheets SUMIF - Как ссылаться на оцениваемую ячейку?

#google-sheets #sumifs

Вопрос:

Обратитесь к таблице выше. В столбце E мне нужна сумма ‘StockCount’ за июль-18. Формула должна учитывать все даты в столбце B (EventDate) и суммировать количество запасов, только если событие относится к тому же месяцу, что и в столбце A.

Я пробую SumIf разными способами, но, возможно, это неправильная функция для использования. Обратите внимание, что я не хочу добавлять дополнительный столбец на лист.

В принципе, я хотел бы что-то вроде if ‘Оцениваемая ячейка’ -Ячейка A < 31 и> 0, ЗАТЕМ добавьте количество запасов.

Ответ №1:

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

 =ArrayFormula(if(A3<>"",sumif(date(year(B$3:B),month(B$3:B),1),A3,C$3:C),""))

(это должно быть введено как формула массива, чтобы преобразовать весь столбец B в даты).

или лучше

 =if(A3<>"",sumifs(C$3:C,B$3:B, ">="amp;A3,B$3:B,"<="amp;eomonth(A3,0)),"")

Но вы также можете использовать эту формулу саморасширяющегося массива в E3:

 =ArrayFormula(if(A3:A<>"",sumif(date(year(B3:B),month(B3:B),1),A3:A,C3:C),""))

1. Этот и приведенный выше ответ сработали для меня (оба, конечно, похожи), но я отметил это, поскольку нашел это более полезным из-за предлагаемых опций. Я все еще не совсем понимаю arrayformulas. Как получается, что =ArrayFormula(if(A3:A<>»»,sumif(дата(год (B3:B), месяц (B3: B), 1), A3: A, C3:C),»»)) делает формулу саморасширяющейся? Это потому, что мы выразили критерий в виде диапазона ? Я понимаю, что это связано с тем, что мы добавили A3: A, но откуда он знает, что он должен использовать «критерий» из каждой последующей ячейки, но при этом использовать весь «диапазон» для тестирования каждого критерия?

2. В SUMIF(диапазон, критерий, [sum_range]) диапазон и [sum_range] всегда являются диапазонами. С помощью единственного критерия SUMIF работает так, что проверяет каждую ячейку в диапазоне на соответствие критерию и добавляет значение sum_range, если оно совпадает, выдавая единый результат (хотя диапазон также может быть массивом, он все равно работает таким же образом). Когда критерием является массив, он выполняет предыдущий «внутренний цикл» для получения единственного результата для первого элемента массива, но теперь есть «внешний цикл», поэтому он повторяет процесс для второго элемента в массиве критериев и т.д., возвращая несколько результатов.

Ответ №2:

вставьте это в ячейку E3:

 =ARRAYFORMULA(IF(A3:A<>"", SUMIF(DATE(YEAR(B3:B), MONTH(B3:B), 1), 
 DATE(20amp;RIGHT(A3:A, 2), TEXT(A3:A, "MM"), 1), C3:C), ))

или это в ячейке E2:

 ={"sum per A column"; 
 ARRAYFORMULA(IF(A3:A<>"", SUMIF(DATE(YEAR(B3:B), MONTH(B3:B), 1), 
 DATE(20amp;RIGHT(A3:A, 2), TEXT(A3:A, "MM"), 1), C3:C), ))}