Calculate values in col B based on value in col B but only calculate final 5 instances

Hi,

I'm trying to calculate the total value of the last 5 entries where the letter equals A. The table below shows 6 A's and their total value = 13. I would like to automatically calculate the value of the last five instances of A.

I've used a formula to calculate the sum of values where the letter equals A (=SUMIF(A:A,"A",B:B)) and I've got a forumla which calculates the value of the last 5 rows (=SUM(OFFSET(B1,COUNTA(B1:B18)-5,0,20,1))) , I now need a way of combining these. Any sugggestions?

Letter Value

A 2
B 4
C 5
A 1
B 2
C 3
A 4
B 3
C 2
A 2
B 4
C 4
A 3
B 1
C 2
A 1
B 3
C 4

Hi,

Thanks for the response.

Sorry I meant I had 13 for all 6 A's but you're right the value is 11 for the last 5.

I've tried your formula but it just shows #NUM... any ideas?

Many thanks

Thanks, I'm fairly new to excel forumals so didn't realise I had to CTRL+SHIFT+ENTER for an array formula.

There will never been an instance when there are less than 5 A's.

It's working now so thanks very much.