where “new” (E5:E15), and “date” (C5:C15) are named ranges. 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: Here again we use the named range “date”, and we concatenate the less than or equal to operator (<=) to the last day of the month, created with the EOMONTH function. 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:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
