11. Unique values per month on a filtered table


=IFERROR(IF(SUMPRODUCT((MONTH($R$22:$R$27)=MONTH(O4))*(SOURCE($Q$22:$Q$27)<>"")*(SUBTOTAL(103;OFFSET($R$22;ROW($R$22:$R$27)-MIN(ROW($R$22:$R$27));0))))=0;"";SUMPRODUCT((MONTH($R$22:$R$27)=MONTH(O4))*(SOURCE($Q$22:$Q$27)<>"")*(SUBTOTAL(103;OFFSET($R$22;ROW($R$22:$R$27)-MIN(ROW($R$22:$R$27));0)))));"")

    This formula is a nested IFERROR, SUMPRODUCT, and SUBTOTAL function that returns the sum of the number of cells in the range SOURCE[BATCH] where the value in the column DATE is in the same month as the current month and the value in the cell is not empty.

    If there are no such cells, it returns an empty string.

    Here’s how it works:

  1.  ROW(SOURCE[DATE])-MIN(ROW(SOURCE[DATE])) returns an array of numbers from 0 to the number of rows in the table SOURCE.

  2.  OFFSET($R$22;...;0) returns an array of cells in the range $R$22:$R$27 where each cell is offset by the corresponding number in the array returned by step 1.

  3.  SUBTOTAL(103;...) returns an array of subtotal values for each cell in the array returned by step 2.

  4.  SOURCE[BATCH]<>"" returns an array of TRUE or FALSE values where each value is TRUE if the corresponding cell in the column BATCH of the table SOURCE is not empty.

  5.  (MONTH(SOURCE[DATE])=MONTH(O9)) returns an array of TRUE or FALSE values where each value is TRUE if the month in the corresponding cell in the column DATE of the table SOURCE is equal to the current month.

  6.  SUMPRODUCT(...) returns the sum of the array returned by step 3 where the corresponding values in the arrays returned by steps 4 and 5 are both TRUE.

  7.  IFERROR(...) handles errors that may occur in the SUMPRODUCT function. If the SUMPRODUCT function returns an error, the IFERROR function returns an empty string.

SUMPRODUCT

Sumproduct - Syntax

SUMPRODUCT(array1, [array2], [array3], ...)


It will sum the number of times a month appears.

At our table:

Arrived types (sumproduct)
0
0
0
0
3
1
0
0
0
0
0
0
MONTH

Month - Syntax

MONTH(serial_number)

Determines the number of the month of the cell.

At our table:

MONTH($R$22:$R$27)
6
6
6
6
6
6
 

                  
At our table:

                    

                       
                        
                          

                            
 MONTH(O4)MONTH(O5)...MONTH(O14)MONTH(O15)
                    
                          
                        
                        
                            
12...1112
SUBTOTAL

Subtotal - Syntax

SUBTOTAL(function_num,ref1,[ref2],...)

Gives us a subtotal of a list. Applying "103" counts cells not empty in a list.

At our table:

DATE
1
1
1
1
0
0
OFFSET

Offset - Syntax

OFFSET(reference, rows, cols, [height], [width])

Determines from which cell should the the subtotal count.

At our table:

Arrived Types (OFFSET)
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
ROW

Row - Syntax

ROW([reference])

Shows us the number of the row.

At our table:

Destiny 2
22
23
24
25
26
27
MIN

Min - Syntax

MIN(number1, [number2], ...)

The lowest row.

At our table:

Destiny 2
2