EXCEL - not an expert

6. Unique values on a filtered table


                =SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q22;ROW(Q22:Q27)-ROW(Q22);;1));IF(Q22:Q27<>"";MATCH("~"&Q22:Q27;Q22:Q27&"";0)));ROW(Q22:Q27)-ROW(Q22)+1);1))

This formula calculates the number of unique values in the range Q22:Q27.

Here’s how it works:

ROW(Q22:Q27)-ROW(Q22)+1 returns an array of numbers from 1 to 6.

IF(SUBTOTAL(3;OFFSET(Q22;ROW(Q22:Q27)-ROW(Q22);;1));IF(Q22:Q27<>"";MATCH("~"&Q22:Q27;Q22:Q27&"";0))) returns an array of values where each value is either a match number or an empty string.

FREQUENCY(IF(...);1) returns an array of frequency counts for each unique value in the array returned by step 2.

SUM(...) returns the sum of the array returned by step 3, which is the number of unique values in the range Q22:Q27

The tilde symbol (~) in Excel is used as an escape character. It tells Excel to treat the next character in the formula as a literal character, rather than as a special character.

For example, if you want to use an asterisk (*) in a formula, but you don’t want Excel to interpret it as a multiplication operator, you can precede it with a tilde symbol.

So, instead of typing =A1*B1, you would type =A1~*B1. The tilde symbol is also used to escape other special characters in Excel formulas, such as the question mark (?), the left bracket ([), and the right bracket (]).

In the formula provided, the tilde symbol (~) is used as an escape character to treat the next character in the formula as a literal character, rather than as a special character. In this case, the tilde symbol is used to escape the ampersand (&) character, which is a special character in Excel formulas.

The formula provided is checking if the cells in the range Q22:Q27 are not empty. If a cell is not empty, it returns the result of the MATCH function.

The MATCH function searches for the value of the cell in the range Q22:Q27 and returns the position of the first cell that matches the search value. The tilde symbol is used to escape the ampersand character in the formula.

SUM

Sum - Syntax

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

                
At our table:
            
               
                
                  
                    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)-ROW(Q20);;1));IF(Q20:Q24<>"";MATCH("~"&Q20:Q24;Q20:Q24&"";0)));ROW(Q20:Q24)-ROW(Q20)+1);1))                    
                  
                
                
                  
                    2

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)-ROW(Q20);;1));IF(Q20:Q24<>"";MATCH("~"&Q20:Q24;Q20:Q24&"";0)));ROW(Q20:Q24)-ROW(Q20)+1);1))
2

If - Syntax

IF(logical_test, [value_if_true], [value_if_false])

                    
    At our table:
                
                    
                    
                      
                        IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)-
                            -ROW(Q20);;1));IF(Q20:Q24<>"";MATCH("~"&Q20:Q24;Q20:Q24&"";0)));
                            ROW(Q20:Q24)-ROW(Q20)+1);1)                    
                      
                    
                    
                        1
                        1
                        false
                        false
                        false
                        false

FREQUENCY

Frequency - Syntax

FREQUENCY(data_array, bins_array)

                        
        At our table:
                    
                       
                        
                          
                            FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)-
                                -ROW(Q20);;1));IF(Q20:Q24<>"";MATCH("~"&Q20:Q24;Q20:Q24&"";0)));
                                ROW(Q20:Q24)-ROW(Q20)+1)                    
                          
                        
                        
                            3
                            2
                            0
                            0
                            0
                            0

FREQUENCY(IF(SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)- -ROW(Q20);;1));IF(Q20:Q24<>"";MATCH("~"&Q20:Q24;Q20:Q24&"";0))); ROW(Q20:Q24)-ROW(Q20)+1)
3
2
0
0
0
0

SUBTOTAL

Subtotal - Syntax

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

At our table:

SUBTOTAL(3;OFFSET(Q20;ROW(Q20:Q24)-ROW(Q20);;1)
1
1
1
1
1

MATCH

Match - Syntax

MATCH(lookup_value, lookup_array, [match_type])

Determines the first row in which the value was found. For instance, the number "6" was found first in number "1" row and again in row number "5", but it will show number "1".

At our table:

MATCH("~"&Q22:Q27;Q22:Q27&"";0)
1
2
2
2
1
6

OFFSET

Offset - Syntax

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

At our table:

OFFSET(Q22;ROW(Q22:Q27)-ROW(Q22);;1)
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

ROW

Row - Syntax

ROW([reference])


                    At our table:
                                
                                   
                                    
                                         ROW(Q22:Q27)-ROW(Q22) OFFSET(ROW(Q22:Q27)-ROW(Q22);;1) ROW(Q22:Q27)-ROW(Q22)+1
                                    
                                        
                                        0 6 1
                                        1 2 2
                                        2 2 3
                                        3 2 4
                                        4 6 5
                                        5 0 6