Closed Thread
Results 1 to 19 of 19

Creating a dynamic table that filters based on multiple data validation filters

  1. #1
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Question Creating a dynamic table that filters based on multiple data validation filters

    Hi Everyone,

    As the title suggests I'm trying to create a dynamic table that filters based on multiple data validation filters.

    The data in the table will be looked up from a raw data tab using SUMIFS.

    I just want to be able to filter the table results based on three drop down criteria (data validation) but where it gets confusing is when you want each of the 3 filters to show "All".

    I don't know how to structure a IF / SUMIFS formula for all the different combinations that the data could be filtered.

    File attached with notes.

    Thank you!
    Attached Files Attached Files
    Last edited by pfusella; 02-26-2025 at 06:55 PM.

  2. #2
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    One way is to specify a default criteria that will include all values in the criteria_range when "ALL" is selected, such as does not equal some random text: "<>λ".

    For example, in cell C12 of you sample workbook:
    Please Login or Register  to view this content.
    Alternatively, with new features like GROUPBY and Trim Refs to generate a single cell report:
    Please Login or Register  to view this content.
    See attached...
    Attached Files Attached Files

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,867

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Are you looking to select all for all three or to select all individually?
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Thank you for the reply - although I'm seeing that the result returned isn't correct. Cell C12 (the yellow cell) should equal $945,787. Although the formula you have in that cell is returning $449,920 (Filter: Type A, ALL, Status A - for Jan24).

    I like the look of the formula as it's super clean! Though are you able to take another look at might be going wrong here?

    Appreciate it. Thx!

  5. #5
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    You should be able to select "All" for all three filters and that effectively gives you no restrictions on the sum data returned (except the correct month).
    But then on top of that you should be able to select any combination of those filters, and it returns the correctly filtered sum data.

    Hope that makes sense!

  6. #6
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Quote Originally Posted by pfusella View Post
    Cell C12 (the yellow cell) should equal $945,787. Although the formula you have in that cell is returning $449,920 (Filter: Type A, ALL, Status A - for Jan24).
    How do you figure it should equal $945,787 for those filters? It returns the same results as if you were to filter the dataset manually...

    EDIT: $945,787 is the expected result for Jan-25, not Jan-24.

    jan24_typeA_stateALL_statusA.png
    Last edited by djclements; 02-25-2025 at 09:22 PM.

  7. #7
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Oh dear - apologies I was summing up Jan25, not Jan24! My bad!

  8. #8
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    No worries, I noticed the same thing almost immediately after posting the screenshot. Cheers!

  9. #9
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Exclamation Re: Creating a dynamic table that filters based on multiple data validation filters

    OK one more for you!

    Same sheet, but this time I need to COUNT and AVERAGE based on the filter criteria.

    Details in the sheet! Thx
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    It would be the same idea as before, but with COUNTIFS and AVERAGEIFS:
    Please Login or Register  to view this content.
    Please note, by referencing the entire date criteria range, C9:O9, the results will spill across each column automatically. If you prefer to use a single formula in each cell to be dragged across, use the following:
    Please Login or Register  to view this content.
    In general, it's not a good idea to use entire column references with these types of formulas, which is why I used rows 2 to 5000. If you need to allow more rows for growth, increase each reference accordingly.

  11. #11
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Fantastic - this works. Appreciate it!

  12. #12
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Hey there! I'm looking at this again and I have a further question that I'm hoping you can help me with.

    For the COUNTIFS formula you provided, I need to ensure that only UNIQUE cells are being counted. I tried to play around with the formula you sent but I couldn't get it to work.


    Your formula:
    =COUNTIFS('Data Stack'!$A$2:$A$50042,$C$35:$Q$35,'Data Stack'!$D$2:$D$50042,IF($C$4="ALL","<>λ",$C$4),'Data Stack'!$G$2:$G$50042,IF($C$5="ALL","<>λ",$C$5),'Data Stack'!$I$2:$I$50042,IF($C$6="ALL","<>λ",$C$6))

    I tried adding =1 around the Data Stack part:
    =COUNTIFS(('Data Stack'!$A$2:$A$50042,$C$35:$Q$35,'Data Stack'!$D$2:$D$50042)=1,IF($C$4="ALL","<>λ",$C$4),'Data Stack'!$G$2:$G$50042,IF($C$5="ALL","<>λ",$C$5),'Data Stack'!$I$2:$I$50042,IF($C$6="ALL","<>λ",$C$6))

    Hope you can help! Thx

  13. #13
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    For unique/distinct counts, I'd probably use a different method altogether. Perhaps something along these lines:
    Please Login or Register  to view this content.
    -OR-
    Please Login or Register  to view this content.
    Last edited by djclements; 05-11-2025 at 01:12 AM. Reason: Corrected typo in the FILTER array.

  14. #14
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Exclamation Re: Creating a dynamic table that filters based on multiple data validation filters

    Thank you for the response! Those formulas are too complicated for me to grasp. I've attached the worksheet where I need them implemented. Are you able to please incorporate those formulas into the worksheet?

    Formula to be inserted into Cell C12 for all the months going across.

    The test is the following...

    For March (Col Q), when the Active/Inactive Providers selector is toggled (row 3):
    ALL = 375
    ACTIVE = 351

    In essence, when looking at the March 2025 data, there are 3 duplicate values that this formula should take care of (Kaur, Rajwinder, Nkola, Yolanda & Thornton, Tory). These names are highlighted in the "Data Stack" tab as duplicate values.

    The formula should also be able to have the Provider Type, State, and Employee Type toggles change the output.

    All the data to reference is in the "Data Stack" tab.

    Thank you!
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Please see my post above! I shared my example workbook in that post

  16. #16
    Forum Contributor
    Join Date
    08-19-2024
    Location
    Philippines
    MS-Off Ver
    MS365 for Business
    Posts
    134

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Same idea, but referencing the Provider column instead of the Prov. ID, plus the additional FILTER criteria:
    Please Login or Register  to view this content.
    Note: there was a typo in my previous reply, which I have since corrected (the FILTER array included 500,000 rows of data when it should have been 50,000).

    Alternatively, a COUNTIFS equivalent could be something like this:
    Please Login or Register  to view this content.
    However, there is a noticeable calculation lag when referencing 50,000 rows of data.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Creating a dynamic table that filters based on multiple data validation filters

    The LET formula worked - ty!
    I might have some more to throw your way - I'll post on here if I do

  18. #18
    Registered User
    Join Date
    05-02-2024
    Location
    Vancouver
    MS-Off Ver
    Office 365
    Posts
    60

    Exclamation Re: Creating a dynamic table that filters based on multiple data validation filters

    @djclements

    I have a really simple one that I'm having trouble with!

    All I'm looking to do is apportion a total cost to a specified period.

    E.g. In the sheet there will be a row with the following data:
    Col A - Amount
    Col B - Period Start
    Col C - Period End

    E.g. I have a wage that I paid for $3269.23 that covered the 2 week period of 22-Mar-2025 to 04-Apr-2025 (period always starts on a Saturday and ends on a Friday).

    I want to apportion that cost correctly to the March period. In theory it should be total cost / 14 days, multiplied by the # days that are in March.

    You can see in the example sheet (Col D) a formula from the internet that I found but I don't think it's doing the correct calc. I have it laid out in the example sheet.

    I just want a clean and elegant formula that works without having to do the manual calc I show above!

    Surely there's an excel function that just does this for us at this point? What do all the accountants do?!

    Thanks! Also just a heads up, I'm going to post this in the main channel also.
    Attached Files Attached Files

  19. #19
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,867

    Re: Creating a dynamic table that filters based on multiple data validation filters

    Administrative Note:

    Welcome to the forum.

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

    Please see Forum Rule #2 about thread duplication.

    I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...d-formula.html

    Thread closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing pivot table filters based on data validation cell value(s)?
    By rhae28 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-07-2023, 02:55 PM
  2. [SOLVED] Macro to select multiple pivot table filters, based on values in a table
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2018, 06:42 PM
  3. Replies: 5
    Last Post: 07-13-2016, 03:39 PM
  4. [SOLVED] Help with Dynamic Validation Lists / Drop Downs / dynamic filters
    By rjnc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2016, 08:10 PM
  5. Creating dynamic chart with 2 filters
    By burlywood66 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-24-2013, 04:45 PM
  6. Updating Two Pivot Filters based on Two Data Validation Cell Text
    By garam082981 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2012, 01:21 PM
  7. Replies: 4
    Last Post: 07-24-2012, 01:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1