Explanation
This formula is designed to be copied throughout the interior of the multiplication table without change. In other words, when the formula is copied to other cells in the table, the references will automatically update as needed to calculate the product of the corresponding row and column.
In $B5, the column is “locked” so that it won’t change, and in C$4, the row is locked.
As the formula is copied, this is what the references look like for the first 5 rows and columns:
| 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|
| 1 | =$B5*C$4 | =$B5*D$4 | =$B5*E$4 | =$B5*F$4 | =$B5*G$4 |
| 2 | =$B6*C$4 | =$B6*D$4 | =$B6*E$4 | =$B6*F$4 | =$B6*G$4 |
| 3 | =$B7*C$4 | =$B7*D$4 | =$B7*E$4 | =$B7*F$4 | =$B7*G$4 |
| 4 | =$B8*C$4 | =$B8*D$4 | =$B8*E$4 | =$B8*F$4 | =$B8*G$4 |
| 5 | =$B9*C$4 | =$B9*D$4 | =$B9*E$4 | =$B9*F$4 | =$B9*G$4 |
Mixed references are a common feature in well-designed worksheets. They are harder to set up, but make formulas much easier to enter. In addition, they are a key strategy for preventing errors since they allow the same formula to be copied to many locations without manual edits.
Explanation
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))
