Forum Discussion

CoolYourJets's avatar
CoolYourJets
Copper Contributor
Oct 18, 2024

Subtotal, but it returns text?

Hello everyone! 

I was wondering if you knew how to help me find an appropriate excel formula. 

Is there a formula like SUBTOTAL, but that returns the visible text value from a filtered table? Here is the situation. 

Basically, I am exporting a list of survey results from clients on Google forms (pg. 1). I then have written an IF() formula to calculate their personality profile score on (pg 2). 

However, I have filtered the IF()formula with a table so I can only see one individual's survey at a time. Because the IF() formula references 1 specific cell, I have to rewrite the whole formula every time I want to calculate a different individual's survey results. Is there a way to filter the survey results and have excel give me the text value from only the visible result of a table search? 

 

This is the list of Survey results. I will then use the IF() formula on a separate page to convert the survey answer to a numeric value which is used in a formula to determine a result. Is there a way to create a filter for the table on page 1 that only shows the visible cells that you searched for? 

 

excel
Formulas and Functions

1 Reply

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 18, 2024

    CoolYourJets 

    This function will return the visible text in a table.  All you have to do is provide the table or column from a table.  If you want to see visible entries in 1 column of the table - provide 1 column, entire table - provide the entire table, etc.

     

    VisibleRows = LAMBDA(table,
        LET(
            arr, TAKE(table, , 1),
            visible, MAP(arr, LAMBDA(v, SUBTOTAL(3, v))),
            result, FILTER(table, visible),
            result
        )
    )

    The attached workbook contains the function.

     

Resources