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))
Explanation
The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to “cancel out” the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.
Working from the inside out, this formula uses boolean logic to “filter” the rate data. The filter is constructed with three expressions, provided as the first argument to SUMPRODUCT:
(dates>=J5)* // date greater than or equal to Dec 3
(dates<J6)* // date less than Dec 7
(rooms=J4)* // room is "King"
Each of these expressions returns an array of TRUE FALSE values:
{FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE,FALSE,TRUE,FALSE,FALSE}
The math operation of multiplying the arrays together coerces the TRUE FALSE values to 1s and 0s, so we can visualize the operation more simply like this:
{0;0;1;1;1;1;1;1;1;1;1}*
{1;1;1;1;1;1;0;0;0;0;0}*
{0,0,1,0,0}
The result is a two-dimensional array like this:
{0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0}
This is the array that does the filtering in this example. If we overlay the filter array on the rate area of the worksheet, it’s easier to understand what’s happening:
Notice the array only contains 1s where we want the rate information to come through. Every other value is zero. When this array is multiplied by the rate data (see below) the zeros will cancel out rate data that is not relevant to room, check-in, and check-out information.
The second argument provided to SUMPRODUCT contains all room rates, in the named range rates (C5:G15). This also is a two-dimensional array:
{65,80,90,105,175;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250;90,105,125,150,250;100,115,125,150,300;100,115,125,150,300;65,80,90,105,300;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250}
Inside SUMPRODUCT, the filtering array described above is multiplied by the named range rates . Both arrays are the same size, the result is an array of the same dimensions. Notice only the rates associated with the stay (as defined by check-in, check-out, and room) have survived the filter:
{0,0,0,0,0;0,0,0,0,0;0,0,105,0,0;0,0,105,0,0;0,0,125,0,0;0,0,125,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0}
Finally, SUMPRODUCT sums all values in the array and returns a final result, 460.
Note: although the rate information is organized with vertical dates and horizontal rooms, it can be transposed and the formula will still function correctly.
With the FILTER function
You can also solve this problem nicely with the new FILTER function :
=SUM(FILTER(INDEX(rates,0,MATCH(J4,rooms,0)),(dates>=J5)*(dates<J6)))
The gist of the solution is that we use the INDEX function to extract just the rates for a King room , and then we feed those rates to FILTER, which extracts just the rates for relevant dates. FILTER delivers these rates to the SUM function , which returns a final result.
