Getpivotdata formula with two variables (month and country)

Hi guys

I am really stuck and I would really appreciate your help with this.

I've attached a sample file to this post to demonstrate what I am trying to do.
I have a pivot table which is showing some data by country and by month.

I'd really like to use the slicer and a Getpivotdata formula to pull out the relevant country information, but based on the current month we are in.
You will see that I have added in a formula in cell A2 to give me the current month (July).

I have highlighted cell E10 in yellow as this is where I would like to add the formula.

So if the slicer was selected for Australia the yellow box should show: 100
If France was selected in the slicer, then the yellow box should show 200, and for US it should show 300.

Does anyone know the formula to do this. I have gone round in circles and just cannot get it work :-(
I'm a novice with Pivot tables and this type of formula, so any help is very much appreciated :-)

Thanks
Debbie

Hi Glenn

Thanks for replying :-)

That formula is great, but it only gives me the total for that country. I need it to return the value for the selected country and only the month we are currently in. So in this example the formula I have set in cell A2 gives us the current month (July).
Do you know how to reference the cell containing the current month in the Getpivotdata formula? I need the value to change automatically each month without any manual interference.

So, if I selected the slicer of Australia, the value it should return is 100.

Thanks
Debbie

Hi, thanks for your reply and for the file. That solution is so close. I was wondering if a subtotal formula would work? So subtotal the values (as that responds to the slicer), and then some how use the getpivotdata formula with it to just extract the current month ... would that work (although I have not idea how to link the two)?

I've added the subtotal formula into the green box.

So close to solving this, any help is much appreciated :-)

Actually, I've changed the way the date is extracted so that it now shows as an actual date, and not just the text of the month name. Not sure if that'll help.

Also just wondering if a double slicer would do the job? I'd rather we could handle it more dynamically without too much manual intervention - but I'll take any suggestions right now :O)