Adding criteria to formula

[Spikyd]

Hi!

I have this formula : It sums the last 6 numbers from the A:A range

=SUM(INDEX(A:A,COUNTA(A:A)-5):INDEX(A:A,COUNTA(A:A)))

How can i add a criteria ? For example i have names in column 2 (B:B) and name 'BOB' is finding as 20x times in this column...
How i can modify this formula to sum the last 6 numers from A:A range having criteria 'BOB' (also 'BOB' if he is 20 times in B:B range i need to get the last 6 'BOBS' if you know what i mean :D)

Thank you !

[canapone]

Hi

an attempt


=SUM(INDEX(B1:B1000,AGGREGATE(14,6,ROW(1:1000)/(A1:A1000="BOB"),INDEX(ROW(1:6),))))

Please, refer to the attachment.

Regards

[Spikyd]

Not working

EDIT : Sorry for making any confusion, is working.
Thank you!

[canapone]

Ciao,

thanks for sharing kind feedback.

If you'd need to sum first 6 Bob's numbers, set first argument of AGGREGATE as 15

Last numbers

=SUM(INDEX(B1:B1000,AGGREGATE(14,6,ROW(1:1000)/(A1:A1000="BOB"),INDEX(ROW(1:6),))))

First numbers

=SUM(INDEX(B1:B1000,AGGREGATE(15,6,ROW(1:1000)/(A1:A1000="BOB"),INDEX(ROW(1:6),))))

regards