How to retrieve the last 5 rows of a Pivot Table using a Dynamic Range

[rca]

I have the following data in a Pivot Table in column C:

Value
10
20
30
40
50
60
70
80
90
100
Grand Total: 550

I would like to create a dynamic range that references only the last 5 records of the Pivot Table: 60, 70, 80, 90, 100.

The formula I'm using is:

=OFFSET($C$2,COUNT($C:$C),0,-5,1)

Unfortunately, this formula references the grand total row. Is there a way to amend the formula such that it only references the 5 rows directly above the grand total row? Thanks!

 

[Scott T]

Try

=OFFSET(C2,COUNT(C:C)-2,0,-5,1)

 

[rca]

I believe this formula will work if there are no blank cells. What if the data were as follows?

Value
10
20
30
40
50
60

80

100
Grand Total: 390

Is there a way to treat blank cells?

 

[Scott T]

Try

=OFFSET(C2,COUNT(C:C)-2+COUNTIF(C2:INDEX(C:C,MATCH(999999999999999,C:C)),""),0,-5-COUNTIF(C2:INDEX(C:C,MATCH(999999999999999,C:C)),""),1)

If you want to remove the zero(blank cells) form the array

=AGGREGATE(14,6,OFFSET($C$2,COUNT(C:C)-2+COUNTIF($C$2:INDEX(C:C,MATCH(999999999999,C:C)),""),0,-5-COUNTIF($C$2:INDEX(C:C,MATCH(999999999999,C:C)),""),1),ROW(INDIRECT("1:"&SUM(--(OFFSET($C$2,COUNT(C:C)-2+COUNTIF($C$2:INDEX(C:C,MATCH(999999999999,C:C)),""),0,-5-COUNTIF($C$2:INDEX(C:C,MATCH(999999999999,C:C)),""),1)<>0)))))