Very cool! Xlookup in SUMIFS 👍

@excelisfun

4 жыл бұрын

Cool, Teammate : )

♨♨♨Magic way in simplifying the difficult🌺🌼🌷

@excelisfun

10 күн бұрын

Glad to help!!!

I always enjoy learning and I always enjoy your videos because I do learn!

@excelisfun

4 жыл бұрын

Glad you enjoy and learn, John : ) : )

Hi Mike.. thanks for the old and new Wednesday fun with SUMIFS, INDEX/MATCH and XLOOKUP. So many great and creative ways to use these functions. Thanks for sharing. Thumbs up!!

@excelisfun

4 жыл бұрын

You are welcome for the Wednesday fun, Old and New, Wyane!!!

Thank you sir for the video. Always get to learn new things from your videos.

@excelisfun

4 жыл бұрын

Glad to help you learn new things, Manohar!!

Thank you for including both old and new methods ... It means a lot for me

@excelisfun

4 жыл бұрын

You are welcome! Old and New: they both are good : )

Awesome Mike. XLOOkUP is the best !

@excelisfun

4 жыл бұрын

Yes, it is : ) : )

Great video. This will be so helpful for work

This is exactly what I needed for a problem that just came up. Amazing that is was right there on your home page, didn't even need to ask the right question in your search. Thanks Mike!!

That is a fun example Mike. Thank you for the video. It is neat to use a lookup formula inside of SUMIFS.

such a great place to learn excel, u r a cool and super guy. thanks a lot of you sir.

You are an amazing teacher, keep it up boss

@excelisfun

4 жыл бұрын

Okay, I will keep it up, Farhan!!!!

I really do appreciate all of these videos. You have definitely helped me with my work productivity.

@excelisfun

4 жыл бұрын

Glad to help with productivity at work, Charging Forward! Love the user name ; )

I like the way you make use of Xlookup, thank you so much Mike

Brilliant as always! Thank you, sir!

@excelisfun

4 жыл бұрын

You are welcome for the brilliance of Dynamic Arrays, Shayne!!!

You make things simple to understand. Thank you, sir.

@excelisfun

4 жыл бұрын

You are welcome for the presentation that tries to make the complex, less complex, prakash!!!

Formula spill is so awesome! Thanks!

@excelisfun

4 жыл бұрын

Yes, it is pretty amazing, the spill : ) :)

Lovin' dynamic array functions! Thanks, Mike

@excelisfun

4 жыл бұрын

You are welcome, Jim!!!!

I am loving the new dynamic array formulas. Great use of xlookup. Thanks Mike.

@excelisfun

4 жыл бұрын

Me too - love spilled arrays! Thanks for stopping by, Matt : ) : )

you are making my work life easier with every new tip, thank you!!!!!

@excelisfun

4 жыл бұрын

You are welcome, Krishna!!!

Wonderful as always. Thanks Mike.

@excelisfun

4 жыл бұрын

Glad it is wonderful for you, Edmundo!!!!

I like your old/new school comparisons. It's very useful! Thanks Mike :)

@excelisfun

4 жыл бұрын

Yes, and we better get used to it, since much of what we did in the past will have a more efficient new way : ) : )

Boom!2 Awesome Formulas..Really Impressed With All The Dynamic Array Functions.Great Stuff Thank You Mike :)

@excelisfun

4 жыл бұрын

You are Boom-Boom welcome, darryl : )

Don't have Excel 365 yet but this is something worthy to learn about. Thank you Mike!

@excelisfun

4 жыл бұрын

You are welcome, orlando!!!!

Thanks Mike. WOW X Lookup. Index and Match still powerful. Loved this!!!! : ) : )

@excelisfun

4 жыл бұрын

It is the Calc Engine which makes the formula amazing, more than XLOOKUP. XLOOKUP only provides alternative to INDEX and MATCH, but the Calc Engine allows us to not lock and not copy : ) Thanks for stopping by, Formula Guy Borg : )

Thanks Mike for this fun. 🎉 💐 🎊

@excelisfun

4 жыл бұрын

You are welcome for the fun! Thanks for the flowers : ) : )

Excellent video, very useful, you think of all users who have diferent versions of Excel. You are an outstanding teacher

@excelisfun

4 жыл бұрын

You are welcome, aguerojg!!!! Glad to help all Excel users : )

It's very useful! Thank you

@excelisfun

Жыл бұрын

Glad it helps!!!

Excellent video, as usual, Mr. Girvin. I am sending this link out to my current and former accounting students. Thank you.

@excelisfun

4 жыл бұрын

Awesome! We love accounting students : )

Thanks a lot!! Very instructive!!

@excelisfun

4 жыл бұрын

Glad it is instructive, Bruno : ) : )

Nice

@excelisfun

4 жыл бұрын

Glad it is nice for you, Phone Excel : )

Thanks!!! Learn something new everyday !

@excelisfun

4 жыл бұрын

You are welcome for the new everyday, mytubeview : )

very nice Mike, Thanks

Xlookup is just amazing .... thanks Mike

@excelisfun

4 жыл бұрын

You are welcome for the XLOOKUP fun, Hussein!!!

Easily my new most used function. I'm so glad that my company switched from Office 2010 straight to Office 365.

Thanks for teaching this amazing feature...

@excelisfun

4 жыл бұрын

You are welcome for the new feature teaching, Kashif!!!

Awesome trick

great as always

As always great video

@excelisfun

4 жыл бұрын

Glad it is great for you and thanks for stopping bu, hakim!!!

Muito legal! Parabéns. Adorei a comparação.

@excelisfun

4 жыл бұрын

: ) : )

Thanks Mike.👍

Great video.. you have inspired me to start my own channel and company with the knowledge I've learned here. Thank -You!!

@excelisfun

4 жыл бұрын

I am glad that you have been inspired, EELT!!!

Awesome!

XLOOKUP & SUMIFS for the win!

@excelisfun

4 жыл бұрын

For the win : ) : ) : ) : )

Thanks for sharing 👍

@excelisfun

4 жыл бұрын

You are welcome for the share, Faisal!!!

Excellent video.

@excelisfun

4 жыл бұрын

Glad it is EXCELlent for you, Ashok!!!

Thanks Mike!

@excelisfun

4 жыл бұрын

Glad you like it, Level Zero!!!

New school method pretty cool :)

@excelisfun

4 жыл бұрын

Yes, indeed, New School is cool : )

Thanks Mike, that's is really cool.

@excelisfun

4 жыл бұрын

Glad it is cool for you, Sajid : ) : )

Excellent

@excelisfun

Жыл бұрын

Glad you like it!!!!!

Hi Mike Hope you are doing fine. Very big thanks to share another amazing video with us Very big thnax👍👍👍👌👌👌💐💐💐 I have 1 query regarding to separate text given jumble data e.g. a1s2f3 my output is i need only asf but without use substitute Please share. Thanks in advance Regards, Ashish

@excelisfun

4 жыл бұрын

Maybe if item in A2: =CONCAT(FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISERROR(--MID(A2,SEQUENCE(LEN(A2)),1))))

@ashishmohan4707

4 жыл бұрын

@@excelisfun thanx👍👍👍💐

its great sir thank you so much

@excelisfun

2 жыл бұрын

You are welcome, Mobin!!!

Brilliant

Awesome

Amazing vedios!! Your videos and tutorials have really inspired me to create more such content. Thanks for the inspiration!!

@excelisfun

4 жыл бұрын

Love it! I Just Subbed to your channel. Cool : )

@FinGeeks

4 жыл бұрын

Thanks a ton 😍👍🏼

@excelisfun

4 жыл бұрын

@@FinGeeks You are welcome : )

i Really appreciate this video

@excelisfun

Жыл бұрын

Glad it helps!!!!

Love it so much

@excelisfun

3 жыл бұрын

Glad you love it!!!!

I like it. A virtual helper column. I've been updating my models with Dynamic Arrays and XLOOKUP and it's saving a lot of steps. I just have to decide which is best to use where, because often I can reach it either way. Here is an alternate new school solution: =SUMIFS(C5:C22,B5:B22,FILTER(I5:I10,J5:J10=E5:E10)) Thanks.

@excelisfun

4 жыл бұрын

Very Nice, Jason M!!!! Your formula is a precursor to the next video where we run an OR Logical test...

very good, thanks

@excelisfun

8 күн бұрын

You are welcome!!!!!

very nice 👍👍👍👍

@excelisfun

4 жыл бұрын

Glad it is nice for you,vijay!!!!

Gratitude!

@excelisfun

4 жыл бұрын

Glad you like it, Ankur!!!

Thumbs up!

@excelisfun

4 жыл бұрын

Thanks, Teammate : ) : )

Love your videos! Quick Question- would Xlookup return an error if someone opens the file with an older version of Office (2016 or non- office 365)?

@excelisfun

3 жыл бұрын

Not at first, but once you put cell in edit mode you will get error. It is not backward campatable.

Fantastic stuff as always! Can you create a video showing how to nest the indirect, xlookup and sumifs in the same formula? Perhaps looking across multiple tabs? Thanks for all of your great work!

@excelisfun

4 жыл бұрын

Glad it is fantastic for you, Daniel. What is the problem that you are trying to solve with INDIRECT, SUMIFS and XLOOKUP?

@danielmain3975

4 жыл бұрын

@@excelisfun Thanks for asking the question. I was just wondering if the same process of nesting a xlookup inside of a sumifs formula could leverage the indirect formula (or any other formula) to do the same process across multiple worksheets. Thanks.

@excelisfun

4 жыл бұрын

@@danielmain3975 Yes, it could. Do you have different lookup tables on different sheets, or what is the set up?

@danielmain3975

4 жыл бұрын

Exactly! Similarly structured lookup tables throughout a workbook.

Cant nearly follow u pace.....have to see it again and again.........👍 thx for u great shared skills.👍

@excelisfun

4 жыл бұрын

You can use the gear icon in the lower right below the video to change the speed of the video ; )

@henriklarsen2276

4 жыл бұрын

ExcelIsFun thx, yes i can. 👍 It was fun, i meant u r so good an fast explaning, i nearly cant follow 😀😉

Hi sir thanks for all the awesome video that you had done It help me alots. May I know how to use excel for interpolation? Is that any video I can learn from?

@excelisfun

4 жыл бұрын

I am sorry, I am not sure how to do interpolation : (

"That IS amazing!"

@excelisfun

4 жыл бұрын

"It IS!!!!!!" The new Excel Engine and Spilled Arrays are so helpful!!! Thanks for stopping by in the comments for videos that are not MSPTDA : )

With this formula you also don't have to copy =SUMIFS($C$5:$C$22;$B$5:$B$22;INDEX(I5:I10;MATCH(E5:E10;J5:J10;0)))

@excelisfun

4 жыл бұрын

Yes! Very cool, 67duiker! But you do need Microsoft 365 Excel ; )

@ankursharma6157

4 жыл бұрын

Brilliant! Thank You for Sharing!

If there are two or more dublicate values at same range for looking up, what should we do for calculation?

@excelisfun

Жыл бұрын

It depends on what you want to do with dups. Lookup functions only get first one. FILTER gets all matching values.

Awesome....on quick question: if hypothetically in the column F there are some duplicates how can we fix it?

@excelisfun

4 жыл бұрын

I am sorry, I do not understand what you are asking.

@daeh49

4 жыл бұрын

Columns F and G are SUMIFS columns so potentially every cell could have the same value as are two in the example.

Could we find multiple positive number against multiple negative numbers and total comes up to zero...

What if the book title "Good Data" wasn't just = to GD14 but also GD15, and GD16, (hard back, digital i guess?) and we wanted to sum units sold for all "Good Data" books sold with multiple book id's?

Hi Sir how to select 3 large numbers above specific number in another column, like I have numbers in column 1-10,20,30,40,50,60,70,..,100 and in column 2-101,105,104,99,..200. I want to extract 3 large numbers from column 2 which are greater than 50 in column 1. Please help me to solve the problem

Let's say we use vlookup formula all the way down to 70k rows , and it makes excel file heavy. Does spilling formula also increase file size and calculation time?

@excelisfun

3 жыл бұрын

If you have spilling formulas in your version of Excel, then you have the new calculation engine which does VLOOKUP and other lookup formulas MUCH faster than in he past. You should be fine with the new Excel on 70 k rows.

@deepk82

3 жыл бұрын

@@excelisfun thank you!

Thanks for sharing! It’s great to see the different lookups in sumifs. 🙂 after watching many tutorials I thought I’d give it a go too, would be great to get some feedback on my channel? 😅

The "best" school: power pivot relational tables

@excelisfun

3 жыл бұрын

School is cool : )

Xlook up Function si just for excel 365?

@excelisfun

4 жыл бұрын

Yes, XLOOKUP and the Spilled Arrays are only in 365.

and what happens when you send a "New school sheet" with xlookup to a person that dosen't have 365?

@excelisfun

4 жыл бұрын

You see the answers but can not edit for formula. If they put it in edit mode, the formula gives an error. Here is a video about this issue: kzread.info/dash/bejne/oJ-MypeIdq7LiMo.html

Is XLOOKUP available in MS 2016 or 2019?

@721MrG

4 жыл бұрын

Unfortunately not. Office 365 exclusive alongside with a bunch of a new functions.

hi I have big email list , some emails contain .com, some .com.au, some contain (com.) and some contain (.com.au.) ,,,,, i need to erase the (dots) in the last two examples, could you help

Nice

@excelisfun

4 жыл бұрын

Glad you like it, Way2Excel!