Get the last N rows of a table
=TAKE(A2:C9,-3)Excel · result on the sample sheet: 3 rows (E2:G4)
Describe what you need and get a working TAKE formula for Excel and Google Sheets, tested on real data before you copy it. TAKE keeps the first or last rows (and columns) of a range and drops the rest.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=TAKE(A2:C9,-3)Excel · result on the sample sheet: 3 rows (E2:G4)
=TAKE(SORT(A2:B8,2,-1),3)Excel · result on the sample sheet: 3 rows (D2:E4)
=TAKE(FILTER(A2:C12,B2:B12=F1),F2)Excel · result on the sample sheet: 3 rows (E4:G6)
=TAKE(FILTER(A2:C12,B2:B12=F1),-3)Excel · result on the sample sheet: 3 rows (E3:G5)
=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))Excel · result on the sample sheet: 4 rows (N2:N5)
=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))Excel · result on the sample sheet: 1546
=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))Excel · result on the sample sheet: 86.00
=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))Excel · result on the sample sheet: $6,005
You describe the task in plain English, and it writes the formula, using TAKE where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
TAKE keeps the first or last rows (and columns) of a range and drops the rest.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the TAKE function page has a practice exercise.