New customers per month
To count new customers by month, you can use a helper column and the COUNTIFS function. In the example shown, the formula in H5 is:
where "new" (E5:E15), and "date" (C5:C15) are named ranges.
How this formula works
This formula relies on a helper column, which is column E in the example shown. The formula in E5, copied down, is:
This formula returns a 1 for new customers and a 0 for repeat customers, and is explained in detail here. Once this formula is in place, the COUNTIFS function can be used to count new customers in each month.
The first range and criteria inside COUNTIFS counts 1's in the "new" column:
without further criteria, this would return a count of all unique customers in the data. However, we want a count by month, so we need to restrict the count to each month shown in column G.
The month names in column G are actually the "first of month" dates: 1-Jan-2019, 1-Feb-2019, and 1-Mar-2019. The dates are formatted with the customer number format "mmm" to display as 3 letter month names: This allows us to write simple criteria to count by month using the dates directly.
To limit the count to only 1's that are greater than or equal to the first of month in column G we use the named range "date" concatenated to the greater than or equal to operator:
To limit the count further to include only 1's the occur by the end of each month, we add one last range/criteria pair:
As this formula is copied down, it returns the count of new customers in each month.
Repeat customers per month
To count repeat customers by month, you can adjust the formula to count zeros instead of 1's. The formula in I5, copied down, is: