Search code examples
google-sheetscountsumgoogle-sheets-formulacountif

Countif with last 5 values

I am trying to calculate the last 5 and last 10 values for specific players in a set of data. The date is in column B, players names are in column K, the data I need to count is in column Z. I just want to have a formula that finds the last 5 values associated with a players name and adds them up. Is that possible? I attached the googlesheet for your review! Thank you for the help!

https://docs.google.com/spreadsheets/d/1iR44nAFSUxZ54LOH61zPOGS_6ugYIMYHEfhJM-SkV6k/edit?usp=sharing

Solution

  • since the dates are in descending order all you need is:

    =QUERY(FILTER({K2:K, Z2:Z}, 
 COUNTIFS(K2:K, K2:K, ROW(K2:K), "<="&ROW(K2:K))<6), 
 "select Col1,sum(Col2) where Col1 !='' group by Col1 label sum(Col2)''")

    enter image description here