Exceljet

Quick, clean, and to the point

New customers per month

Excel formula: New customers per month
Generic formula 
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Explanation 

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:

=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))

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:

=(COUNTIFS($B$5:B5,B5)=1)+0

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:

=COUNTIFS(new,1

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:

=COUNTIFS(new,1,date,">="&G5

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:

=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))

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:

=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.