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...

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!

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!

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

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

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

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.

Fantastic - this works. Appreciate it!

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

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!

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

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.

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

@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.