Відео

Thanks, Justin, this really helped me. All the steps are broken down and easy to digest. For Google Spreadsheet users, you will need to include the "=ArrayFormula({})" formula. So it would be =ArrayFormula({max(ROW(A:A)*NOT(ISBLANK(A:A)))})

Hello Justin, Thank you for the great video. It helped me to sum the alternate columns in my table. I have a model where there are multiple columns [36 columns] - Actual, Budget and Variance for 12 months. Is it possible to create a dynamic model where the sum total of the YTD can be based on criteria? For ex- Criteria YTD Oct Actual [from a drop down list] - Captures sums of Jan-Oct, but ignore Nov-Dec in the range ? with the current formula, its sums everything in the range. Appreciate a reply.

Usefull on both tips, thanks

Thanks for this video. I have this project I'm working on that looks at attendance by Month, Year and Employee. I have created this let formula =LET(d,Data[ATTENDANCE DATE],e,Data[EMP NAME],p,Data[Position],m,Data[MONTHLY],i,Data[Include], sl,TRANSPOSE(SORT(UNIQUE(e),,-1)), vl,SORT(UNIQUE(d),,1), cl,SUM(m), fr,HSTACK("Start of Month",sl,"Total Payroll"), sr,HSTACK(vl,SUMIFS(m,d,vl,e,sl),SUMIFS(m,d,vl)), tr,HSTACK("Total Hours", SUMIFS(m,e,sl),SUM(cl)), VSTACK(fr,sr,tr)) I have a column in the Data set called Include =SUBTOTAL(3,[@[EMP NAME]]) Using the Pivotvy function it will filter the Data based on Slicers. The issue is that Pivotby is not available in the Customers current 365 Version. Theye do have the Filter and Let functions. How would i add the Filter function into the Let formula above.

Any suggestion on how to only include unique values? I've tried using the "unique" formula but it doesn't work.

Nice. How could you bring the template lambda's into an existing Excel file?

"Wow, excellent teacher! Thank you, man, it was great. You also taught wildcard scenarios excellently."

Can we merge the blanks cell with the above cell having values

bro ty so much

how can I search based on the ending of words? *end doesn't work because it finds "end" anywhere in the word, and ?end finds it anywhere as long as it's not at the beginning because it requires at least one preceding character. Neither option specifies anything about what happen if there is no wildcard after the string being searched.

How could I do this if each group had a set distribution? For example, group A should be assigned to 40% of participants, group b 20%, group c 30%, and group d 10%?

Thanks dear! Brilliant, Logical, easy!!! Solved my problem!!!

I have been searching for a way to do this for way too long. I'm not an office guy. I will work on this method but how do I do it the easier way you suggested is available with 365?

This was one of the best videos I've ever watched. The time and consideration you put into your visuals to help the viewer better understand what you were saying (using blue arrows and orange arrows to differentiate between the occurrences or using a border around the range you were talking about) is great. Seriously man keep up the great work. Definitely subscribing to your channel.

A piece of genius. This has solved a challenge I could not figure out.

Amazing video, thanks. I would need another extension of this formula. I need the sum of the QTRs, however for a particular product lead. How can I put filter on both row and column at the same time? Appreciate your help.

Great Solution! Thank you!!

does the formula in E2 work in google sheets?

Life changer for me thnx

This is a very good video with excellent information. Unfortunately, it was very hard to follow and replicate because your video did not zoom into what you were doing or explaning and I could not read the formulas that you created. This made it very difficult to implement your excellent information. Recommend looking at some other like tutorial videos (like this one: ua-cam.com/video/_JfsEWfyv_E/v-deo.html) and learn how to zoom in and out so people can see what you are actually doing, especially demonstrating/explaning the formulas are concerned.

Hi, Thanks for the video; if I have a Microsoft 365 license, what would be the process!?

Tried on 2013 and 2019 versions, but formula A returns N/A and formula B returns always 1.As another user mentioned below, results is seen in function arguments only but not in sheet

both formulae always return N/A when appears on sheet or when tracking, howevrer the correct answer will be shown when using the function argument pane but still appear on sheet as N/A

Can you help me calculate the total sum from the sum-product formula? S.NO EXPENSES HEAD Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 I need total of BANK 1 by sumproduct formula

Can you help me calculate the total sum from the sum-product formula? S.NO EXPENSES HEAD Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 I need total of BANK 1 by sumproduct formula

Thank you~~

This was a lifesaver. I had almost 8k of rows with over 80% that were blank in a column and this made it take less than a minute to fix.👍

Hi really appreciate this video, one issue i had when emulating this with my project is that it wont update new data due to overlapping pivot tables, i have the same spacing as you and nothing underneath each pivot table. what should i do?

Hey I loved your video. But I couldn’t find your formula you used in video because the video was blurry. It would be really great if you could share the excel file.. Please Please 🙏 Please

What if your lookup value is an entire column of values?

Thank you so much for that video; I have been looking for this for so long. I have followed your instructions step by step, and it is working well. I have one question, though - not really an error, just an annoying thing. For example, the division I chose is Div 07, and when I expand the dropdown of Salespeople, it shows me the names of salespeople assigned to that Div 07. However, at the top of the dropdown, it is displaying Div 07 as the first Salesperson option. I'm not sure where I made a mistake, and I would appreciate information if you know what I should correct. Thank you.

Worth noting (just cause I just went through this pain) - MATCH will allow you to use 'TRUE' as its first parameter, but that only works when you use the ampersand syntax & to string together your criteria. For this asterisk and parentheses syntax, using the '1' as the first for 'true' is critical. Microsoft Office documentation is always SO hard to sift online, so thanks so much for this video.

Very nice I did not know you could use the & symbol. Beats the hell out if my ifs combined with xlookup

so much work

Which Excel version is this

Nice & Neat Approach, Thanks for Sharing And thanks for the bonus tip 🤓

This worked but once you got the info copied how do you remove that formula without changing the data if you need to do other things? All the solutions I've found don't work or not working right. It should be as simple as clicking "remove all formulas" but I see no such option.

This is perfect!!!!! It was exactly what I needed. I found another tutorial to return Yes or No since that is what I needed instead of name or line number.

Thank you for this great video 📹

Thank you so much bro, even after having alcohol I can’t able to sleep cause I was keep on searching for the solution. Your video helps me a lot to got the solution now I can sleep.

Amazing! Thanks.

Awesome video 📹 👏 Thank you very much for sharing your knowledge with us :)

is there a way to make this more dynamic by referencing cells that have the word "Widget" or "Gadget" in it. Then you could change one of the cells to "DooDad" and change your filter criteria that way? I just don't like typing out "Widget" within the formula

Great trick, thank you.

Friggin awesome!

Formula A only works if the table/range starts at A1, any other location will always return 0. Only Formula B correctly provides the last non-empty row regardless of where the range is placed withing the worksheet

Perfect Video 📹, really insightful. Thank for sharing ur knowledge with us

U have made videos with really cool excel tricks such filter function with row numbers

Good luck we enjoy your excel videos

Good luck. Hope to celebrate 1M subscribers with you soon.