Загальна формула
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Резюме
Щоб підрахувати нових клієнтів за місяцями, ви можете скористатися допоміжним стовпцем та функцією COUNTIFS. У наведеному прикладі формула в 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 можна використовувати для підрахунку нових клієнтів щомісяця.
Перший діапазон і критерії всередині COUNTIFS підраховує 1 у стовпці "new":
=COUNTIFS(new,1
без подальших критеріїв це призведе до підрахунку всіх унікальних клієнтів у даних. Однак нам потрібен підрахунок за місяцями, тому нам потрібно обмежити підрахунок кожним місяцем, показаним у стовпці G.
Назви місяців у графі G фактично є датами "першого місяця": 1 січня 2019 року, 1 лютого 2019 року та 1 березня 2019 року. Дати відформатовані у форматі номера замовника "ммм" для відображення у вигляді назв місяців із трьох літер: Це дозволяє нам писати прості критерії для підрахунку за місяцями, використовуючи дати безпосередньо.
Щоб обмежити підрахунок лише одиницями, які перевищують або дорівнюють першого числа місяця у стовпці G, ми використовуємо названий діапазон "дата", об'єднаний більшим або рівним оператору:
=COUNTIFS(new,1,date,">="&G5
Щоб додатково обмежити підрахунок, включаючи лише одиниці, що відбулися до кінця кожного місяця, ми додаємо останню пару діапазонів / критеріїв:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Тут ми знову використовуємо названий діапазон "дата", і ми об'єднуємо оператор менше або рівний (<=) останньому дню місяця, створеному за допомогою функції EOMONTH.
Коли ця формула копіюється, вона повертає кількість нових клієнтів за кожен місяць.
Повторюйте клієнтів щомісяця
Щоб підрахувати повторних клієнтів за місяцями, ви можете налаштувати формулу для підрахунку нулів замість одиниць. Формула в I5, скопійована нижче, така:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
