02:47 Bonus Formula #1 03:16 Bonus Formula #2 Lots of fun with this one. Many ways to do it, but my guess is that the SUMIFS will be fastest: =SUMIFS(XLOOKUP(C12,$C$1:$E$1,$C$2:$E$9),$A$2:$A$9,A12,$B$2:$B$9,B12) =SUMIFS(INDEX($C$2:$E$9,,MATCH(C12,$C$1:$E$1,0)),$A$2:$A$9,A12,$B$2:$B$9,B12) =SUMPRODUCT(INDEX($C$2:$E$9,,MATCH(C12,$C$1:$E$1,0)),--($A$2:$A$9&$B$2:$B$9=A12&B12)) =SUM(FILTER(XLOOKUP(C12,$C$1:$E$1,$C$2:$E$9),($A$2:$A$9=A12)*($B$2:$B$9=B12))) =SUMPRODUCT($C$2:$E$9*($C$1:$E$1=C12)*($A$2:$A$9=A12)*($B$2:$B$9=B12)) =SUM(FILTER(FILTER($C$2:$E$9,$C$1:$E$1=C12),($A$2:$A$9=A12)*($B$2:$B$9=B12)))

Another great video on XLOOKUP! Good job Mike

@excelisfun

4 жыл бұрын

Thanks, Chris!

just one word: AWESOME! thank you very much . your explanations are very clear. all the best.

@excelisfun

4 жыл бұрын

Glad it is clear for you, elhassan!!!

Great MIKE always give new thing in excel

@excelisfun

4 жыл бұрын

Glad you like the new things, Amit!!!

And my fvourite lookup adding - so easy! Excel Is Magic!Thanks for sharing :)

@excelisfun

4 жыл бұрын

You are welcome for the share, Malina : )

Xlookup is very Amazing, Thanks Mr. Mike.

@excelisfun

4 жыл бұрын

You are welcome Stat Guy Ogwal!!!!

Your the man thank you for all you do.

@excelisfun

4 жыл бұрын

You are welcome, Ricardo!!!!

Had to come back to this video today and utilize it. Thanks Mike!

Great video that shows how the supercharged XLOOKUP can in turn supercharge other Excel functions. SUMIFFS is only one of many functions that benefit from XL and the "new"calc engine.

@excelisfun

4 жыл бұрын

Yes, only one of many : ) : ) : )

Hi Mike, Thanks for another great video. The bonus 2 was fun.

@excelisfun

4 жыл бұрын

You are welcome for another, fun one with bonus, Khalid!!!

Hi Mike.. great video.. love when you show the various different ways to get to the same result and also the good explanation of delivering a range object to the various IFS functions with XLOOKUP and INDEX. Thumbs up!

@excelisfun

4 жыл бұрын

Thanks, Wayne!!!!

@nuthannuthan479

2 жыл бұрын

Hi Mike, can you please teach us how to do it in power query. Thanks in advance

So many lookup options. Thanks for the great lookup video Mike.

@excelisfun

4 жыл бұрын

You are welcome, N Sanch01!!!

Wonderful video mike; I like INDEX & MATCH combo with SUMIFS :)

@excelisfun

4 жыл бұрын

Cool! Glad you like it, Sachin!!! I like it too : )

thank you!! this was really helpful!

@excelisfun

2 жыл бұрын

Glad it was helpful!

Thank you Mike, a really useful...as always!

@excelisfun

4 жыл бұрын

You are welcome, Paul!

Excel Master.Amazing video's channel .

Thanks Amazing Mike for this EXCELlent video.

@excelisfun

4 жыл бұрын

Glad to provide EXCELlent fun, Syed : ) : )

Great as always!

@excelisfun

4 жыл бұрын

Thanks, Teammate pmsocho : )

Thanks Mike. Amazing Staff!!! It is recommended that to learn the 'Nuts and Bolts' of the XLookup, the videos should be downloaded to study. :) :)

@excelisfun

4 жыл бұрын

Yes, nuts are bolts are fun!!!!!

Sir, i have been a fan of all your videos and always refer to your videos when I need any help with excel. I am an accountant and struggling to look up the function that will help me look up the one criteria in multiple sheets and add the value from all those sheets into my index page. Is there a simple way of doing this ? The Xlookup and vlookup function is great when I am just dealing with one sheet.

well explained!

@excelisfun

4 жыл бұрын

Glad it helps, Steven Nye!!!!

Hey, Have you uploaded video related to different uses of Xmatch particularly and with xlookup? And i want to know the alternate to Sumifs..is it possible?

thanks you

@excelisfun

8 күн бұрын

You are welcome!!

Nice example of using the new XLOOKUP! For the SUMPRODUCT solution, we may simplify it a bit, without using INDEX, MATCH. =SUMPRODUCT($C$2:$E$9,($A$2:$A$9=$A12)*($B$2:$B$9=$B12)*($C$1:$E$1=$C12))

@excelisfun

4 жыл бұрын

Yes, it may simplify it, but it will take MUCH longer to calculate on a large data set than using SUMIFS with either XLOOKUP or INDEX. In the book I wrote, Ctrl + Shift + Enter, Mastering Excel Array Formulas, I timed and compared many Array Formulas and the consistent pattern emerged that Direct Array Operations almost always take longer to process than Built in Functions. The Direct Array to pick out the column: $C$1:$E$1=$C12 will take longer than the built in functions to lookup a row like INDEX and XLOOKUP. Also, the Direct Array Operations for row conditions, $A$2:$A$9=$A12 and $B$2:$B$9=$B1 will take longer than calculating the conditions in SUMIFS. As I discuss in my book, and in 100s of videos I have posted, you will not notice any performance slow down on a small data set or a small workbook with only a few formulas to calculate, but when you get larger data sets or large models with many formulas, you will feel the slowness. The reason for all this is fairly simple: when making direct array operation, Excel has to calculate an answer for each cell in all the ranges, but with built in functions, they rely on the program code which is almost always faster than doing the calculation cell by cell. An Interesting note: I have not done much timing of formulas since the new calculation engine came out, but Excel MVP Charles Williams has done a lot of timing and he says that the new engine is faster in many ways. Even with that, my guess is that cell by cell calcs will be slower.

@wmfexcel

4 жыл бұрын

ExcelIsFun thank so much for the detailed explanation. 👍🏻

@excelisfun

4 жыл бұрын

@@wmfexcel Go Team!!!!

How would you do this if you wanted only 2 criterias? Meaning you looked up california and then product? When I do it this way like you are it gives me a value? Would it just be a sumif and not sumifs?

Thanx

Good video, Mike. How are we users to know that INDEX and XLOOXUP deliver a range object rather than an array object? MS documentations doesn't mention it.

@excelisfun

4 жыл бұрын

We don't. Microsoft doesn't ever tell us. And is not directly documented. We just have inferred it since SUMIFS and the like are programmed to only accept ranges and what is delivered by INDEX and OFFSET and now XLOOKUP are accepted by SUMIFS and the like. At least that is what I wrote in my book after lots of tests and form discussion over the years.

@drsteele4749

4 жыл бұрын

@@excelisfun How monolithic of them. I can't begin to describe the toils I've had creating complex formulas with what should obviously be MAXIFS, MINIFS SUMIFS, only to get error messages. I usually just give up on them and multiply arrays inside SUMPRODUCT to avoid the painful troubleshooting. Thanks tons for you awesome insight, Mike!!!

@excelisfun

4 жыл бұрын

@@drsteele4749 SUMPRODUCT is very handy and has rescued us for decades, luckily : )

again , other option could be FILTER like in =SUM(FILTER(FILTER($C$2:$E$9,$C$1:$E$1=C12),($A$2:$A$9=A12)*($B$2:$B$9=B12))) . Interesting to know , like Mike always did regarding timing , which one of various methods can run faster for large data sets.

@excelisfun

4 жыл бұрын

Well, it would be interesting to time, but almost always, direct array operations (like ($A$2:$A$9=A12)*($B$2:$B$9=B12))) will be slower than built in functions like INDEX or XLOOKUP (to lookup row) or SUMIFS. I wrote the book Ctrl + Shift + Enter, Mastering Excel Array Formulas, and in that book I did a lot of timing, and that was the general conclusion. But, although that is the rule that holds most of the time, you always have to time to make sure : )

@excelisfun

4 жыл бұрын

LOVE your inventive formula, cr gr0912!!!!

@Excelambda

4 жыл бұрын

@@excelisfun I know your CSE course, your CSE course should be the holly grail of excel learning, even with the new help of dynamic arrays stuff , people should know how the inner organs of excel are functioning . I am positive that the best VBA programmer is the one who knows how to do it also without VBA, that's the challenge, to understand the guts of excel, something that you do at the best.

Hello Sir, A B C 1.20 40 30 2.(10) 50 [60 3. 20 30 20 4. 50 10 10 5. 60 10 80 6. 30 (90) 50] 7. 20 20 70 In above eg. A2 has minimum value & B6 has maximum value, so from this range 2-6 I want sum of column C2-C6 (60+20+10+80+50). so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C. I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner. I m providing one more eg. so that u have better understanding. A B C 1. 50 30 30 2. 60 20 40 3. (30) 10 [50 4. 90 80 60 5. 70 (90) 30] 6. 50 70 20 7. 40 40 10 from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30]. Please help with this question. Thank You.