function

[Afarag]

Dears,

I ask for a help in function, I have to pivot tables contain the same column headers and the same first row,
the second pivot is connected in slicer to filter it,
i ask to count the average in the 1st table based on what criteria selected in 2nd pivot via slicer

Thanks,

 

[Luke M]

In the actual workbook, are the two pivot tables coming from the same data source? If so, we could link a single slicer to both PT's and then calculating the average is a breeze.

 

[Afarag]

Hi,
the two pivots are coming from a different source. I thought to use slicer link but there is not available,
the source data is hidden in the same sheet.

 

[Luke M]

Hmm. Try this, in cell C20. Copy to the right as needed.
=SUMPRODUCT(ISNUMBER(MATCH($B2:$B6,$B$11:$B$18,0))*C2:C6)/(COUNTA($B$11:$B$18)-1)

Not the most robust formula, but hopefully "good enough".

 

[Afarag]

Luke M

As usual you give the awesome response, great function meets the desirable outcomes

Gratefully,

 

[Afarag]

Luke M

As usual you give the awesome response, great function meets the desirable outcomes

Gratefully,

But I see your formula need one simple modification is to dispense with "-1" at the end of function due to match the two criteria in pivots
to be "=SUMPRODUCT(ISNUMBER(MATCH($B2:$B6,$B$11:$B$18,0))*C2:C6)/(COUNTA($B$11:$B$18))"

thanks a lot for help,