Using SumProduct to return values based on criteria when date filter changes

[JaimeS1]

Hello, I have multiple pages with varying data. I have date slicers on each page to filter by date. I have a summary page with a section that breaks down the number of cases or phases on the corresponding pages. I have another section on the summary page that breaks down the number of cases or phases worked by different team members. I was able to find a formula that updates the number of cases or phases based on the date filtered on the corresponding page. I am having trouble figuring out how to update the number of cases or phases by team member name when the date filter changes. I hope I've explained it clearly. Any help would be greatly appreciated.

[AliGW]

What is the formula meant to be doing? There is nothing in B8, so the last bit of it is unclear.

[protonLeah]

summary b2 (count per case/phase):

Please Login or Register  to view this content.

average (C2):

Please Login or Register  to view this content.

phase/cases per team member (B10, C10,...)

Please Login or Register  to view this content.

Included several pivot tables also.

*Note that tables on B Cases & MM cases named same as sheet...
*randomizing formula in column E just for testing averages...

[JaimeS1]

Hi AliGW, I apologize. That formula is incorrect. I did not need the last part so the correct formula to calculate the B Cases in B2 of the Summary tab is...SUMPRODUCT(SUBTOTAL(3,OFFSET('B Cases'!A:A, ROW(A:A)-MIN(ROW(A:A)),,1)))-1. This is giving me the number of B Cases based on the month and year I select in the date slicer on the B Cases tab.
I am looking to calculate the number of B Cases, B10 on the Summary tab, linked to the Team Member, A10 on the Summary tab, when the date slicer is changed on the B Cases tab. I hope that's clearer. Thank you for your time.

[JaimeS1]

Hi protonLeah, I tried your formula for phase/cases per team member (B10, C10...) but I keep getting a #REF! Invalid cell reference error. I am entering the formula on B10 of the Summary tab and exactly how you have the formula in your response. I must be missing something. Is there supposed to be information in the quotations after B$9? Thank you for your time.

Hi protonLeah again, In looking at your formula, I noticed that on your workbook, when I filter by Team Member name on the B Cases tab that it does change the B Cases count on the Summary tab. However, I am looking for a way to be able to show how many B Cases a Team Member has, B10 on the Summary tab, when I filter the date on the B Cases tab. The goal would be that all Team Members on the Summary tab would update to show how many cases they had for the date selected on the B Cases or MM Cases tabs. I hope that makes sense. Thank you again for your time.

[protonLeah]

1) I'm sorry. I didn't explain the named ranges (just that note at the bottom). For the two sheets, B Cases & MM Cases, I renamed each table to correspond to the column header on the Summary sheet, A2/B9 & A3/C9. Summary B9 = "B Cases" but range names can't have spaces, so the table is named B_Cases. The SUBSTITUE function replaces the space in "B Cases" with "_" to make the table name.

2) checking...

[JaimeS1]

Good morning protonLeah, I was able to figure out problem 2. But...I have a new problem. I'm having trouble finding the right formula to sum B4:B7, D4:D7, and F4:F7 on Sheet1 based on the date selected on Sheet2. In other words, a way to total the number of the different phases worked by each Team Member based on the date of Sheet2. Thanks again for all your help!

[protonLeah]

B4,D4, F4:

Please Login or Register  to view this content.

C4:

Please Login or Register  to view this content.

* modified sheet1 headers
* note table on sheet2
added useless macro toy

[JaimeS1]

Awesome! Thank you so much!!