Return the Max value from Pivot Table Totals

im not too hot at pivot tables but if you construct another as in attached then use array formulas in yellow cells then that might work, but........ it may be possible just to use the pivot itself to do this!!!!!!!!!

If you're not going to be applying sub filters on resulting PT then it would make most sense to calculate against the raw data.

Using your sample:

K23: =MAX(IF(($I$4:$I$13=$I$20)*($M$4:$M$13<>0),$M$4:$M$13))
confirmed with CTRL + SHIFT + ENTER

K24: =MIN(IF(($I$4:$I$13=$I$20)*($M$4:$M$13<>0),$M$4:$M$13))
confirmed with CTRL + SHIFT + ENTER

(if you don't have negative values in K you can obviously modify the above - part. the MAX)

Returning name associated with those values is invariably a little trickier:

J23: =INDEX($K$4:$K$13,MATCH(1,INDEX(($I$4:$I$13=$I$20)*($M$4:$M$13=$K23),0),0))
confirmed with Enter as normal
copied to J24

If your raw data is always sorted by Date you can make the above more efficient by determining appropriate range based on data first hand if so desired.


If it is your intention to filter the PT and have the results update accordingly (eg exclude Supv2 would change results) then let us know. Calculating from the PT is possible but more complicated (obviously) and if you don't need to I wouldn't.