How to reference user selected value from a filter

New Contributor

I am trying to reference the item a user picks from a filter. I cannot use a drop down list. 


For example, there is a column where a filter is applied. User selects 1 item in the list. How can I reference what the user picked in a formula?


I am trying to subtotal hotel data based on the selected property. The data will include multiple properties, user selects one, data displays for just that property. I cannot use subtotal as this data also needs to be separated by month and there is no Subtotalif function. 

5 Replies
best response confirmed by Hans Vogelaar (MVP)


This might work if you only need to reference 1 selected item in the filter (If you need multiple, another formula can be drawn up).


Yes, this works!

If you don't mind, what would it be if multiple items were selected?


Using the same sample workbook:

Thank you so much, these both worked beautifully.
You're welcome. Glad it worked!