Forum Discussion

Rodgie22's avatar
Rodgie22
Copper Contributor
Jan 22, 2025

COUNT UNIQUE VALUES WHILE FILTERING

Hi,

I need some help regarding my file.

I have managed to count total unique values in column L which is 4,075, however if I apply filter on the dates that are not blank, it still showing total unique values of the whole column L. 

(screenshot only, I filtered it by date of welding.)

this is the formula I use:
=SUM(IF(FREQUENCY(IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""),IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""))>0,1))

Looking for some assistance. Thank you

excel
Formulas and Functions

3 Replies

  • BackslashLambda's avatar
    BackslashLambda
    Copper Contributor
    Jan 24, 2025

    SUBTOTAL(3, range) will count non-empty cells in a range but only include visible rows. Subtotal is the only function that takes autofilter into account.
    Add the bold part to your existing formula and it will do what you want.

    =SUM(IF(FREQUENCY(IF(LEN(L10:L12194)*BYROW(L10:L12194,LAMBDA(x,SUBTOTAL(3,x)))>0,MATCH(L10:L12194,L10:L12194,0),""),IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""))>0,1))

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jan 23, 2025

    Attach a sample file and show your desired output. What is your Excel version? If it is M365 then could utilize UNIQUE() function with FILTER().

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jan 23, 2025

    Take this:

     

    Create a Helper Column: Let's assume column M is your helper column. In cell M10, enter the following formula and drag it down to apply to all relevant rows:

    =IF(ISBLANK([DateColumn]), "", [ValueColumn])

    Replace [DateColumn] with the column that contains your dates, and [ValueColumn] with the column that contains your values (column L in your case).

     

    Use an Array Formula: Now, to count the unique values in the helper column M, use an array formula. Enter the following formula in any cell:

    =SUM(IF(FREQUENCY(IF(M10:M12194<>"", MATCH(M10:M12194, M10:M12194, 0)), IF(M10:M12194<>"", MATCH(M10:M12194, M10:M12194, 0))) > 0, 1))

    Make sure to press Ctrl+Shift+Enter to enter it as an array formula.

Resources