Project Burndown Chart- Slice and display only visible data

Hello,

I'm wondering if there is anyone here that can help me with a burn down chart I have built for a project.
It was tough trying to find anything online regarding excel burn down charts so I built one using this youtube tutorial:
" YouTube .com/watch?v=AR2KgOYAqU8&list=PLWd3I4Gj-9XuEPPs1nGTvVK56K3Zc1PFD&index=3 "

I would like to have the ability to dynamically change the burndown chart using slicers for the MS# and Ref# columns though.
I'm looking to find a way to populate the Planned and Actual columns ('Burndown Chart Data') with visible data only once 'Source Data' table is sliced or filtered.

I was thinking of using a SUMPRODUCT, SUBTOTAL and OFFSET formula in the burn down chart table to help fix the COUNTIFS issue with displaying only visible data like in the article below:
" exceljet. net/formula/count-visible-rows-only-with-criteria#:~:text=To%20count%20visible%20rows%20only%20with%20criteria%2C%20you,criteria%20like%20COUNTIF%20or%20SUMIF%20without%20some%20help "

The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.
I'm having issued trying to get it to display correctly. Just wondering if some of the Excel wizards on here can find a solution.



This is how the Burn down chart is setup:

Data from a SharePoint list is imported into Excel with Power Query and then displayed in the 'Source Data' table

The 'Burndown Chart Data' table then extracts the Planned and Actual dates from 'Source Date' and counts the dates within the range of the Date field in 'Burndown Chart Data'.

The formula used for this is:
Planned Completion:
=COUNTIFS('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))

Actual Completion:
=IF(A3>TODAY(),NA(),MAX(B$1:B$29)-COUNTIFS('Source Data'!G:G,">="&$A$3,'Source Data'!G:G,"<="&A3))


The issue I'm finding is that the COUNTIFS formula counts all data in the 'Source Data' Planned and Actual columns and not just visible (sliced) data.

The formula I was modifying and hoping to display visible data only was something like this:
=SUMPRODUCT(('Source Data'!F:F,">="&A3,'Source Data'!F:F,"<="&MAX(A$1:A$29))*(SUBTOTAL(103,OFFSET(A3,ROW('Source Data'!F:F)-MIN(ROW('Source Data'!F:F)),0))))



Thanks for your help!

-Jason

Just FYI, I have posted this to 1 other excel forum:
mrexcel. com/board/threads/project-burndown-chart-slice-and-display-only-visible-data.1139364/

Thanks Andy!

That works perfectly. I spent hours yesterday playing with SUMPRODUCT, SUBTOTAL and OFFSET but did find AGGREGATE late in the day and was trying some different things. The way you put it together is really nice. Thanks again for this.

Take care