Общая формула
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Резюме
Чтобы подсчитать новых клиентов по месяцам, вы можете использовать вспомогательный столбец и функцию СЧЁТЕСЛИМН. В показанном примере формула H5 имеет следующий вид:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
где «новый» (E5: E15) и «дата» (C5: C15) являются именованными диапазонами.
Объяснение
В этой формуле используется вспомогательный столбец, которым в показанном примере является столбец E. Копируемая формула в E5:
=(COUNTIFS($B$5:B5,B5)=1)+0
Эта формула возвращает 1 для новых клиентов и 0 для постоянных клиентов и подробно объясняется здесь. Как только эта формула введена, функция СЧЁТЕСЛИМН может использоваться для подсчета новых клиентов в каждом месяце.
Первый диапазон и критерий внутри COUNTIFS засчитываются 1 в столбце «новый»:
=COUNTIFS(new,1
без дополнительных критериев это вернет количество всех уникальных клиентов в данных. Однако нам нужен счет по месяцам, поэтому нам нужно ограничить счет каждым месяцем, указанным в столбце G.
Названия месяцев в столбце G фактически являются датами «первого месяца»: 1 января 2019 г., 1 февраля 2019 г. и 1 марта 2019 г. Даты отформатированы в формате номера клиента «ммм» для отображения в виде трехбуквенных названий месяцев: это позволяет нам написать простые критерии для подсчета по месяцам, напрямую используя даты.
Чтобы ограничить счет только единицами, которые больше или равны первому числу месяца в столбце G, мы используем именованный диапазон "date", объединенный с оператором "больше или равно":
=COUNTIFS(new,1,date,">="&G5
Чтобы дополнительно ограничить счет, чтобы включить только единицы, произошедшие к концу каждого месяца, мы добавляем последнюю пару диапазон / критерий:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Здесь мы снова используем именованный диапазон «date» и объединяем оператор «меньше или равно» (<=) с последним днем месяца, созданным с помощью функции EOMONTH.
Когда эта формула копируется, она возвращает количество новых клиентов за каждый месяц.
Повторных клиентов в месяц
Чтобы подсчитать постоянных клиентов по месяцам, вы можете настроить формулу для подсчета нулей вместо единиц. Копия формулы I5:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
