Hi Alan.. INDEX, MATCH and COUNTA to the rescue. Excellent! Your clever examples and solutions are teaching my brain how to solve many problems. Thanks and Thumbs up!
Genius!! Just solved an issue that will save me a lot of work!!! I no longer have to change the formula, each month, to the newest populated column. Virtual fist bump, Alan!
Hi! A very crisp video. Thanks for taking the time. Can you shed a little insight on how to use this for data that are in a single column? Fetch the last entry in column D, for example. I tried =Index(Range),1,0 and many variants to get it to work but with no luck
@computergaga Thanks for what you do! One thing about google sheets... for any array formulas like this you must add the array formula function. then you're off to the races.
@@Computergaga Hello Can you help on this Where do I see to copy MS Excel file path ???? Normally when right button click on mouse it will see but It not showing... Please help
This has worked well for me, thank you. Only problem is I have two cells that are coming up blank - nothing in them. They have exactly the same calculation as all the others.
Hi! Great video and very helpful. Just the solution I was looking for. It worked on most of my data but in a few rows, it did not pick the last value and instead displayed a zero. How can I correct this, any suggestions?
This is very helpful!! Question: How would you handle roll over numbers using these formulas? For example, let say Product E has rolled over after reaching 1000, thus your value for week 22/11/2018 is 153 instead of 1153. Now, you want to calculate the increase between this week and last week. How would you account for this in your formula? I'm doing something similar for meter readings and they roll over at a certain point. Thank in advance for any recommendations you can offer:)
Very helpful! My case is a little different, but it should work, I have a string of values in 8 columns of a row, and need to figure out the last value that is not a number and not 0. So, essentially last number > 0. I am guessing the COUNT criteria just changes.
Very useful video. What if we have to choose column header of last value's of row instead of its own value. Please help with formula for the same, if you could...
You're welcome. For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.
What if the cell before the last value is blank? Why the last value is not showing when the cell before is empty ? Can you help me with this problem please ??
Alan, this video was really helpful. What if I want the column header (Date in your example) returned for the last, non-blank cell in each row? Do you know how I can setup the function? Thanks for your anticipated assistance.
Hi Allan, thank you so much. I was wondering if I want the date of the last occurring numbers how do I change the formula? Because some of the date will not have data in it.
True, so we would use a slightly different approach. We could count the blanks and add them. Or format the range as a table and use the COLUMNS function with it.
The COUNTA function counts all non-blank cells, so will include numbers, text, and errors. Alternatively though, the COLUMNS function can be used. This function returns the total number of columns in a given range, table, or array.
For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.
great video! was really helpful. i used the index formula to pull data for calculation. One question, how can i keep the data thats being pull for calculation to not changed when new data are being added to the table? thank you.
Well the formula is automated to always return the last row even when the table expands. But if you don't want this you can remove the formula. Other options could be Power Query or a macro.
Thanks for the video, very helpful. Only problem I am having is that some cells in my row are empty, so it doesn't include them in the counta function, which means the counta value is less than it should be. Any way to overcome this?
I want do following this how can I enter formula for this 1) When C1 is equal to D1 then store D1 value in next sheet/ in this sheet 2) again when C1 is equal to D1 then store D1 value in next sheet/ in this sheet below above value Give me the solution of this
Thank you for sharing. Loved the tutorial and I thought I found a solution to my problem; however, the formula is not working? It is just showing #REF! and not the last value? I tried semi colon as suggested below? that didn't work for me. I have used a name array (ID1_BEST5) in a motorsports points table which highlights the total points at the end of each rd (total of 10 rds in the championship) not highlighting ever race that makes up a round (e.g. 3 races = 1 rd), so ID1_BEST5 cells are H9, L9, P9, T9 and so on. I want/need to display each riders last points they scored for a round. Here is the formula I used from your tutorial =INDEX(ID1_BEST5,1,COUNTA(ID1_BEST5)) No idea why this is not working because your lesson made so much sense. If I copy (and link the cells) from each rd total and put them next to each other and/or name the array with a new name, it works, but this seems like a lot of extra steps? Thank you, I have been reading the net for hours/days trying to find a solution.
I have another problem, if you have the time for it. I like to retrieve values from 14 different cells in a row over a period of 14 days into one cell. Each day i put in a value in a new cell(yellow) and this new value shall replace the value from the day before and presented in the same cell(blue).The blanks cells between the yellows is formulars and can not be changed. I can not figure out to create this formula. Is there a solution for this? A1 is blue. B1, C1, E1, G1, I1, K1, M1, O1, Q1, S1, U1, W1, Y1, AA1 is all yellow
I'm not exactly sure what you mean. It sounds like this video is exactly what you want. Retrieving the last value in the row of yellows and putting it in the blue cell.
That would present a problem. Empty cells are rarely helpful. The best solution would be for this range to be formatted a table. Then COLUMNS can be used instead of COUNTA.
Great video but you made one mistake in calculating the difference %. Formula should be (Current-Prior)/ Prior. In this case (636-527)/527 or 20.7% difference.
![일상 속 기분 좋음 [Proto disco meme]](http://i.ytimg.com/vi/DwmEIaZ7XGQ/mqdefault.jpg)
Wowwwwwww 💓💓💓💓😍😍 thank you💓💓😍
Welcome 😊
Hi Alan.. INDEX, MATCH and COUNTA to the rescue. Excellent! Your clever examples and solutions are teaching my brain how to solve many problems. Thanks and Thumbs up!
Thank you Wayne.
Genius!! Just solved an issue that will save me a lot of work!!! I no longer have to change the formula, each month, to the newest populated column. Virtual fist bump, Alan!
Thanks Sandy.
I have been looking for this solution for a long time, thank you!
You're welcome, Braulio.
Always great to watch, very helpful instructive and well explained. A definite 'go to' resource
Thank you Geoff 👍😊
Hi!
A very crisp video. Thanks for taking the time.
Can you shed a little insight on how to use this for data that are in a single column? Fetch the last entry in column D, for example. I tried =Index(Range),1,0 and many variants to get it to work but with no luck
@computergaga Thanks for what you do! One thing about google sheets... for any array formulas like this you must add the array formula function. then you're off to the races.
Thanks, Bram.
Beautiful work. Thanks for sharing
You're very welcome Syed.
It works. God bless you. Thanks 🙏
You're welcome!
Thank you!
Im new with google sheets/excel. Been struggling with this formula, finally i have it
You are welcome, Gabriel.
Works like a charm! thanks
You're welcome!
I'm a big fan of you after watching this video
Great! Thank you.
@@Computergaga
Hello Can you help on this
Where do I see to copy MS Excel file path ????
Normally when right button click on mouse it will see but It not showing...
Please help
Thank you so much for this video!!
No problem 😊
This has worked well for me, thank you. Only problem is I have two cells that are coming up blank - nothing in them. They have exactly the same calculation as all the others.
verrryyyy helpful, thank you :)
You're welcome 👍
Just what i needed right now
Happy to help.
Really really helpful ...!good job!
Thank you, Buddhika.
Great example! But what if I am looking for text comments entered & not numbers?
Thank you! The COUNTA function will count all non-blank cells, so text values too 😊
Great description sir, you should get 10 likes
Thank you very much 👍
Thank you so much.
You're welcome, Mansour.
Hi! Great video and very helpful. Just the solution I was looking for. It worked on most of my data but in a few rows, it did not pick the last value and instead displayed a zero. How can I correct this, any suggestions?
Brilliant explanation. Thank you!
Thank you Per.
Very good tutorial👍🏻
Thank you Phyllis.
Hi Allan, Good Job But I have one query if I want above mentioned Date instead of Numbers which formula is suitable can you please share Formula..
Change the row number in the first range of the INDEX function to use row 2 instead of row 3. Everything else stays the same.
This is great, thank you!
You're welcome, Rodrigo.
This is very helpful!!
Question:
How would you handle roll over numbers using these formulas? For example, let say Product E has rolled over after reaching 1000, thus your value for week 22/11/2018 is 153 instead of 1153. Now, you want to calculate the increase between this week and last week. How would you account for this in your formula? I'm doing something similar for meter readings and they roll over at a certain point. Thank in advance for any recommendations you can offer:)
I got it!
Thank you!
You're welcome 👍
Very helpful! My case is a little different, but it should work, I have a string of values in 8 columns of a row, and need to figure out the last value that is not a number and not 0. So, essentially last number > 0. I am guessing the COUNT criteria just changes.
Great .. go on
Very useful video. What if we have to choose column header of last value's of row instead of its own value. Please help with formula for the same, if you could...
You're welcome. For the last header value using this data. The formula can be:
=INDEX(A2:NZ2,,COUNTA(A2:NZ2))
This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.
Hey Allan what is the logic of counta in the index column section that lets you capture the last value?
The COUNTA function counts all no empty cells. So here it counted the values in row 2 returning 6 which is the last used column i.e. current week.
What if the cell before the last value is blank? Why the last value is not showing when the cell before is empty ? Can you help me with this problem please ??
yes okay, the formula is working now. thanks.
Excellent!
Thanks
You're welcome Asiri.
Alan, this video was really helpful. What if I want the column header (Date in your example) returned for the last, non-blank cell in each row? Do you know how I can setup the function? Thanks for your anticipated assistance.
Great! You can use the same formula, Abimbola, but reference C2:CZ2 instead.
Hi Allan, thank you so much. I was wondering if I want the date of the last occurring numbers how do I change the formula? Because some of the date will not have data in it.
Sure. Just edit the ranges used in the INDEX and COUNTA functions to look along the rows of dates and not the values.
Thanks for the sharing, can you please let me know if there is 0 in data & we need to find data instead 0, what would be the formula?
I need this too??? Help
I tried the first one in a column, returned #REF, time for more troubleshooting!
👍
Just one quotation before counta
Hi, how about if there is any empty cells in between the columns, this can't be work at all
True, so we would use a slightly different approach.
We could count the blanks and add them. Or format the range as a table and use the COLUMNS function with it.
@@Computergaga thanks
Can you please suggest same for Date? I am in need
Depends more specifically on what you are asking, Kanishka. The technique in the video can return dates, if that is what you are asking.
Brilliant thank u so much
You're welcome. Thank you Ayoub.
Thank you! it is helpful
You're welcome, Arzu.
What if the data is '0012346 in cell GE
Text format?
The COUNTA function counts all non-blank cells, so will include numbers, text, and errors.
Alternatively though, the COLUMNS function can be used. This function returns the total number of columns in a given range, table, or array.
Is there a way to get the date of the last value in that column?
For the last header value using this data. The formula can be:
=INDEX(A2:NZ2,,COUNTA(A2:NZ2))
This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.
Thanks for the video...
You're very welcome Tushar.
This only works when you have the column before the data range..... correct?
No, you can return the result to anywhere.
Thanks!
You're welcome Ricardo.
Is there a way to do this in Power Query?
I'm positive there is, but I'm not sure of the approach to write here. Sounds like a nice challenge Victoria.
Great video, but if there are blanks in the data it gums it up a bit.
For sure. Blanks can be naughty in Excel. Using the header row for the count is more reliable, unless you have a blank column.
You could use the following formula to find the last value in one row
=index(yourvaluecells,,match(9.9e307,yourvaluecells,1))
@@Computergaga pls share formula
great video! was really helpful. i used the index formula to pull data for calculation. One question, how can i keep the data thats being pull for calculation to not changed when new data are being added to the table? thank you.
Thank you. To stop it from updating when new data is added, you could to remove the formula. Select the cell(s), copy and paste values.
Computergaga thank you for the reply. This is what we are doing now. Was hoping it can be automated to reduce human error. ☺️
Well the formula is automated to always return the last row even when the table expands. But if you don't want this you can remove the formula.
Other options could be Power Query or a macro.
Thanks for the video, very helpful. Only problem I am having is that some cells in my row are empty, so it doesn't include them in the counta function, which means the counta value is less than it should be. Any way to overcome this?
Thank you Carlos. you could run the COUNTBLANK function also and add them together.
@@Computergaga Thank you so much! I spent hours trying to find this resolution!
I want do following this how can I enter formula for this
1) When C1 is equal to D1 then store D1 value in next sheet/ in this sheet
2) again when C1 is equal to D1 then store D1 value in next sheet/ in this sheet below above value
Give me the solution of this
Thank you for sharing. Loved the tutorial and I thought I found a solution to my problem; however, the formula is not working? It is just showing #REF! and not the last value? I tried semi colon as suggested below? that didn't work for me.
I have used a name array (ID1_BEST5) in a motorsports points table which highlights the total points at the end of each rd (total of 10 rds in the championship) not highlighting ever race that makes up a round (e.g. 3 races = 1 rd), so ID1_BEST5 cells are H9, L9, P9, T9 and so on.
I want/need to display each riders last points they scored for a round. Here is the formula I used from your tutorial
=INDEX(ID1_BEST5,1,COUNTA(ID1_BEST5))
No idea why this is not working because your lesson made so much sense.
If I copy (and link the cells) from each rd total and put them next to each other and/or name the array with a new name, it works, but this seems like a lot of extra steps?
Thank you, I have been reading the net for hours/days trying to find a solution.
Thank you Bill. I cannot tell from your formula what the problem might be though.
When i put in the formula=index(b4:k4,1,counta(b4:k4))a vindow pops up and tell me that there is a failure somewhere!why will it not work for me?
Could be that you need semi colons instead of commas =INDEX(B4:K4;1;COUNTA(B4:K4))
I have another problem, if you have the time for it. I like to retrieve values from 14 different cells in a row over a period of 14 days into one cell. Each day i put in a value in a new cell(yellow) and this new value shall replace the value from the day before and presented in the same cell(blue).The blanks cells between the yellows is formulars and can not be changed. I can not figure out to create this formula. Is there a solution for this? A1 is blue. B1, C1, E1, G1, I1, K1, M1, O1, Q1, S1, U1, W1, Y1, AA1 is all yellow
I'm not exactly sure what you mean. It sounds like this video is exactly what you want. Retrieving the last value in the row of yellows and putting it in the blue cell.
How i can get last value in a row when is text format? Thanks
This technique will work with text also Silvio.
@@Computergaga With text, this not work for me. =INDEKS(B3:NE3,1,COUNTA(B3:NE3)) not work
Maybe you have blank cells in the range. COUNTA will count all non blank cells from B3:NE3.
@@Computergaga yes i have blank cells... have any other formula for my problem?
There is a function named COUNTBLANK. To find the last row, you need to add the COUNTA and COUNTBLANK results together.
What if the row has an empty cells in between the values
That would present a problem. Empty cells are rarely helpful.
The best solution would be for this range to be formatted a table. Then COLUMNS can be used instead of COUNTA.
SALUTE TO YOU 😻
😊
Great video but you made one mistake in calculating the difference %. Formula should be (Current-Prior)/ Prior. In this case (636-527)/527 or 20.7% difference.
Good point David. Well spotted. Thank you for your comments on the video.
how about last value in a row
That is what the video is showing. The same tech pique can be applied to a column though. Just adjust the ranges.
getting a 0 value..:(
TLDR: =index(range:range,1,counta(range))
Wats that ?