Reset drop down using data validation

[marc01]

Hi there,

Hoping someone can help, wondering if this is possible without the use if VBA/macros....

To set the scene I am looking at a tennis match, and counting where a player hits a return dependent on where the serve lands. I have a drop down menu already that allows the user to select the player returning. All is working well with the following formula:

=SUM(COUNTIFS(RawDataTable[Side],"Deuce",RawDataTable[Serve placement],"Body",RawDataTable[Returner],'Report - return data'!$C$45,RawDataTable[Return direction],"Middle"))

However, I was looking to add in another drop down menu that allows the user to select the 'Set score'. I used data validation again to create a drop down menu of "Set 1", Set 2", Set 3", etc. The following formula works great:

=SUM(COUNTIFS(RawDataTable[Side],"Deuce",RawDataTable[Serve placement],"Body",RawDataTable[Returner],'Report - return data'!$C$45,RawDataTable[Return direction],"Middle",RawDataTable[Set score],'Return patterns'!$B$3))

However, I would ideally like the user to be able to clear the Set score or select "All" set scores to show all data irrespective of the set score.....problem is the raw data column I have for Set Score doesn't have "All" so when this is included in the data validation range and then selected from the set score drop down menu it returns 0.

Hope this makes sense. Does anyone know away to work around this?

Best,

Marc

 

[marc01]

Or is there any way to create a slicer (Set Score) for the RawDataTable, and then embed this within the countifs formula?