SUM the Last "N" Non-Blank Numeric cells using the OFFSET function -Microsoft Excel
Вставка
- Опубліковано 11 жов 2024
- In this video, I show you how i find the sum of the last 3 non-blank numeric cells by incorporating the OFFSET function, which is very versatile
Workbook can be found here
drive.google.c...
Very nice concept.I love the way you make complex concepts look really simple.
Thanks a lot 😊
Thank you so much for your time and work into this! Really appreciate it! I checked the link but unfortunately do not see what you did there. as al the Sums are currenlty 0. If N= 1 for Jones the sum should be 101 (33+33+35). Excluding the first filled cell 52. Hope to you how that works:)
I guess it is because some of the functions are not available in Gsheets or the version of excel being used. In cell I3, I am using a formula like =IFERROR(SUM(DROP(FILTER(C3:H3,C3:H3""),,$I$1)),"") which you can drag now. You can try it in Excel online, to see how it works
Really appreciate this video! Exactly what I need. How do I do this but instead of sum the last N How do I do it for the first (N) filled cells. Or total sum of everything except the first N amount of filled cells
I added 2 tabs in the linked workbook to demonstrate that scenario. Hopefully you have some of the new functions like TAKE and DROP. docs.google.com/spreadsheets/d/1lqx9kQXl1ygZFH2FWq9cGeEIF3qrIHLk/edit?usp=sharing&ouid=115481529442131367093&rtpof=true&sd=true
@@ExcelMomentsThank you so much for your time and work into this! Really appreciate it! I checked the link but unfortunately do not see what you did there. as al the Sums are currenlty 0. If N= 1 for Jones the sum should be 101 (33+33+35). Excluding the first filled cell 52. Hope to you how that works:)
Hi i check your google drive link, i found out the sheets SumLastN_Form_Upd and it works perfectly well. But i wonder is there any altenative since sequence doesnt available in excel 2016 version. I need to do it in another computer with excel 2016 version. Please help. Thanks in advance.
Hello Ivan, Thanks for your comments. I can't test in 2016 at the moment as I dont have that version installed, but prior to SEQUENCE we used the ROW9INDIRECT) construct, so you can try something like this
=SUM(INDEX($A3:$H3,LARGE((C3:H3"")*(COLUMN(C$3:H$3)),ROW(INDIRECT("1:"&$I$1))))) and provide feedback on whether it works or not
That was helpful. Thank you. How would you go about summing the last three scores if there were random blank cells in the range?
Hello Rahul, I have included a 2nd tab in the linked workbook to show how i would have done that
Essentially, I need to check if those cells are NOT blank, get their column numbers, choose the largest N column numbers (which would be the last X non blank), Use an INDEX to pick up the values in those cells(columns) and use a SUM to add them all up. Something like that
@@ExcelMoments Thanks for the illustration! Your explanation of the sequential operations helped a lot.
@@rahul7270 Thanks for the feedback
This is what im looking for. but is it possible to ignore 0 and/or blanco. for example if you have 1, 1, 0, 0, 1 is the answer is 3.
I am assuming you are using Office 365, so you have access to the newer functions
assuming data is in C9 to H9, then try =SUM(TAKE(FILTER(C9:H9,C9:H90),,-3)). I would appreciate the feedback
@@ExcelMomentsLet me put it this way. How to use sumifs instead of sum. becasue i cant complete formula.
@williamchen9496 I am not sure I follow your statement. You want to SUM up ignoring blanks and zero, but only for the last 3 non-zero, non-blank values. Any reason why you don't want to use the SUM? Or the formula option I sent
@@ExcelMoments I want to make a analyse so i need to chance the range everytime. Sometimes there are 2 or 5 blanco in a row
@@ExcelMoments =SUM(TAKE(TOROW(J6:Q6,1),,-3))
How can you do this in google sheets? Doesn't allow negative offset parameters, i.e. not allowing -3?
In that case, you may be better off starting from 3 columns to the end and going rightwards. That is =sum(OFFSET($C3,0,COUNT($C3:$H3)-3,1,3)),......so instead of OFFSET by -1 to get to the last non-blank, i offset by -3, so that with 3 columns to the right, I can get what i want
@@ExcelMoments Genius, thankyou!
@@Adam-dn8xc You are far too kind 😄😄
Hi I am sorry I am bothering you again but I cannot find it back in your sheet. Also the formula does not work for me. My question in short is 'How can I measure the total sum of non black numeric cells except the first N amount of Cells '
The workbook i referenced is here docs.google.com/spreadsheets/d/1lqx9kQXl1ygZFH2FWq9cGeEIF3qrIHLk/edit?rtpof=true&sd=true#gid=1749443415 and i also showed the formula I used, =IFERROR(SUM(DROP(FILTER(C3:H3,C3:H3""),,$I$1)),"")
What version of Excel are you using?
@@ExcelMoments Thank you so much!!!! It worked indeed!!!!!! You are the best!