Dude -- you just helped me solve the formula I've been trying to figure out for my business for the past decade, literally...maybe even longer. I just utilized what I learned here into my spreadsheet, hit 'enter', and after it worked, i nearly cried tears of joy in my living room. I'm grinning from ear to ear right now. This is amazing.
Flipping brilliant Thank you so much for this post I've been struggling with this issue all night ! So much cleaner and simpler than other solutions and the filter refinement is very useful Also great for let's say getting the last 7 seven entries in a row to have last week's daily data by adding a -n suffix e.g. =INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-7) =INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-6) =INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-5) etc. etc. =INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-0) Thanks again
If anyone else is having trouble with this it should be ; not comma to separate the values in the INDEX function. Though I think this might be related to locale settings. For example, where I'm from we use 3,14 instead of 3.14 to say "three point fourteen".
Dude -- you just helped me solve the formula I've been trying to figure out for my business for the past decade, literally...maybe even longer. I just utilized what I learned here into my spreadsheet, hit 'enter', and after it worked, i nearly cried tears of joy in my living room. I'm grinning from ear to ear right now. This is amazing.
SUPERB! MUCH BETTER THAN THE OTHER EXPLANATIONS ON TH-cam!!! THANK YOU
Finally found this after hours and hours! Thank you so much man, you rock.
Flipping brilliant
Thank you so much for this post
I've been struggling with this issue all night !
So much cleaner and simpler than other solutions and the filter refinement is very useful
Also great for let's say getting the last 7 seven entries in a row to have last week's daily data by adding a -n suffix e.g.
=INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-7)
=INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-6)
=INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-5)
etc. etc.
=INDEX(FILTER(2:2,2:2""),COUNTA(FILTER(2:2,2:2""))-0)
Thanks again
Thanks! That helped a lot
Thank you. Glad you've found it helpful
worked very well - thank you so much
Thank you!
Great explanation!
Brilliant, thank you so much!!
Thank you.
Worked perfectly for what I needed :)
\
When I use the Array formula in the column then the last value of this column in the other cell is not showing: any solution?
Thanks. This was killing me!!
NICE finally i got it after two three days
Sir, How to get the last value from the different file like in this video?
Thank u
AMAZING
If anyone else is having trouble with this it should be ; not comma to separate the values in the INDEX function. Though I think this might be related to locale settings. For example, where I'm from we use 3,14 instead of 3.14 to say "three point fourteen".
even one year later, your comment saved me from insanity... cheers mate
@@Roosvelen Glad to hear that! I surely lost my mind 😂
Can you do this but across sheets?
Yes this does work across sheets in the same workbook. It works the same way, just your ranges will be on a different sheet
I've tried and tried this but I keep getting the first entry instead of the last one. What could the problem be?
Legend
What if instead of getting the last value in a column, you need to get the values from the last 3 cells in a column??
And the column is not static.. but grows
Somehow it doesnt work for me
:(
to whom it may concern: =MAX(IFS(A1:A"";MAX(A1:A);0;1))
Thank you!
AWESOME!! THANK YOU SO MUCH!!