Пікірлер

• 
  @michaeldingee74319 күн бұрын

  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.

• 
  @Sparkles79Ай бұрын

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

• 
  @Michael_AlaskaАй бұрын

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

• 
  @sajjadabouei67212 ай бұрын

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

• 
  @tanusaini68972 ай бұрын

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

• 
  @user-ue4oe9dy3e2 ай бұрын

  bro ty so much

• 
  @danielarivera98552 ай бұрын

  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.

• 
  @holliewatson74733 ай бұрын

  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%?

• 
  @rajivramchandra5 күн бұрын

  Hi Hollie! Did you find an answer to your question? I'm trying to solve a similar problem. Thank you!

• 
  @sunphysics3 ай бұрын

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

• 
  @Novoct63 ай бұрын

  Thank you !

• 
  @jasoneaton45143 ай бұрын

  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?

• 
  @joeschmoe75633 ай бұрын

  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.

• 
  @graemegourlay28503 ай бұрын

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

• 
  @agnideepmukherjee17634 ай бұрын

  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.

• 
  @huseyinburaktasci16384 ай бұрын

  Great Solution! Thank you!!

• 
  @user-kb5ge6hs3k5 ай бұрын

  does the formula in E2 work in google sheets?

• 
  @user-kb5ge6hs3k5 ай бұрын

  This is the error I'm getting: "Did not find value 'TRUE' in MATCH evaluation."

• 
  @ArnoVanderNeut-jm6oi5 ай бұрын

  Life changer for me thnx

• 
  @joycemoore80986 ай бұрын

  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: kzread.info/dash/bejne/kX6a1aeQltzcj6g.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.

• 
  @Mohamed.GadAllah6 ай бұрын

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

• 
  @aliv.81446 ай бұрын

  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

• 
  @rickmurphy65 ай бұрын

  In Excel 2013 i'm seeing =MAX(ROW(A:A)) always returns 1, but (note ROWS vs ROW) =MAX(ROWS(A:A)) returns 1048576. But that doesn't help Formula B, which is now always returning 1048576 . in 2013 i'm trying =MATCH( MAX(A:A), A:A, 1) which seems to work (i.e. find the last row) with numbers and dates, but not chars. For chars i can use a large string as in =MATCH( "zzzzzzzz", A:A, 1). I feel like i'm going astray.

• 
  @user-ft8sc8tw5m6 ай бұрын

  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

• 
  @hiteshkothari13577 ай бұрын

  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

• 
  @hiteshkothari13577 ай бұрын

  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

• 
  @fongfongfong19787 ай бұрын

  Thank you~~

• 
  @jamesrawls44267 ай бұрын

  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.👍

• 
  @anthonyliang457 ай бұрын

  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?

• 
  @jackaryan75467 ай бұрын

  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

• 
  @user-ob4zc3qj6b8 ай бұрын

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

• 
  @natalianatalia28 ай бұрын

  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.

• 
  @rpt-xk9qm9 ай бұрын

  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.

• 
  @bertjanwijnhoven92729 ай бұрын

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

• 
  @smatsri10 ай бұрын

  so much work

• 
  @dkalambokis783 ай бұрын

  Anything with more than 10 letters must baffle you, isn't it?

• 
  @ranjitbera49510 ай бұрын

  Which Excel version is this

• 
  @heshamfouad888610 ай бұрын

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

• 
  @ExcelSmith10 ай бұрын

  Thanks. Glad you like it. 😀

• 
  @jaden808410 ай бұрын

  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.

• 
  @user-nx9ut2gl1r10 ай бұрын

  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.

• 
  @ExcelSmith10 ай бұрын

  You're welcome. I'm glad this helped.

• 
  @nadermounir822810 ай бұрын

  Thank you for this great video 📹

• 
  @ExcelSmith10 ай бұрын

  You're welcome. I'm glad you liked it.

• 
  @gchethan947211 ай бұрын

  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.

• 
  @ExcelSmith10 ай бұрын

  You're welcome. Glad the video helped so you could get some sleep. 😀

• 
  @katestan700211 ай бұрын

  Amazing! Thanks.

• 
  @ExcelSmith11 ай бұрын

  Thanks. I hope it helped.

• 
  @nadermounir822811 ай бұрын

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

• 
  @ExcelSmith11 ай бұрын

  My pleasure. 😀

• 
  @quintonlyvers817011 ай бұрын

  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

• 
  @ExcelSmith11 ай бұрын

  Thanks for the question. This is definitely doable. For example, using the equation from the video, we could place “Gadget” in cell P2 and “Widget” in cell P3. Updating the equation with references to these cells instead of the hardcoded values gives us: =FILTER(B5:G24,((C5:C24=P5)+(C5:C24=P6))*(E5:E24>5000)*(F5:F24>3000),"No value") We can then change the value in cell P2 from “Gadget” to “Doodad” to update the filtered results. I hope this helps.

• 
  @quintonlyvers817011 ай бұрын

  @@ExcelSmith this did! Thanks a lot!

• 
  @ExcelSmith11 ай бұрын

  That's great. Glad it helped. 😀

• 
  @user-ji7ts5tw8o Жыл бұрын

  Great trick, thank you.

• 
  @ExcelSmith11 ай бұрын

  Thanks. I'm glad you liked it. 😀

• 
  @500magnum4 Жыл бұрын

  Friggin awesome!

• 
  @ExcelSmith Жыл бұрын

  Thanks. 😀

• 
  @Hobbes2099 Жыл бұрын

  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

• 
  @ExcelSmith Жыл бұрын

  Thanks for the comment. Formula B is definitely more flexible with regards to the data's location and structure.

• 
  @nadermounir8228 Жыл бұрын

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

• 
  @ExcelSmith Жыл бұрын

  Thank you. Glad it was helpful. 😀

• 
  @nadermounir8228 Жыл бұрын

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

• 
  @ExcelSmith Жыл бұрын

  Thanks. I hope they were helpful.

• 
  @nadermounir8228 Жыл бұрын

  Good luck we enjoy your excel videos

• 
  @ExcelSmith Жыл бұрын

  Thank you. I appreciate it. 😀

• 
  @abdullahquhtani4247 Жыл бұрын

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

• 
  @ExcelSmith Жыл бұрын

  Thanks. I like that goal. 😀

• 
  @Abdulaziz-dh4rt Жыл бұрын

  Thanks for the great work and great video. I made it to reach up to aligning all the data using the pivot table, what stopped me is the If function in which I couldn’t do. I faced an issue with using @Region or in my case @Description and couldn’t make it to appear in light blue font as in the formula. I couldn’t do the whole function with the true and false statements as well. I would really appreciate it If you could help me by any mean to get through this.

• 
  @ExcelSmith Жыл бұрын

  Thanks for the question. The first two things I would check are 1) that the data on the “Form” worksheet is set as an Excel Table and 2) that the equations in columns L through O are entered as array formulas. 1) If the data is set as an Excel Table, you will see a “Table” tab in the ribbon when you have selected any of the header cells (row 2 in the video example). If you don’t see the “Table” option in the ribbon after selecting one of the headers, with a single header cell selected, select “Insert” and then “Table”. 2) If the equations in columns L through O were entered as an array formula, you will see curly braces surrounding the equation when you have the cell selected without the cursor being active in the formula bar. If you’re not seeing the curly braces, to enter the equations as an array formula, with the cursor active in the formula bar, press CONTROL + SHIFT + ENTER to submit the equations, as opposed to just ENTER. If theses suggestions don’t solve the issue, please let me know the timestamp from the video where it’s not working for you so I could provide some additional details. It would also help If you could provide your equation, if possible. I hope this helps.

• 
  @kganesh3886 Жыл бұрын

  Pls share with excerise attachment to me then i will be click thumb ups

• 
  @ExcelSmith Жыл бұрын

  Thanks for the comment. Here's the link to the Excel file: docs.google.com/spreadsheets/d/1pIVAr68qzGutgBfQquccwV_DiR0Lgs29/edit?usp=sharing&ouid=112840883257889972521&rtpof=true&sd=true