Double OR Logical Tests in SUMIFS Function using FILTER or IF or MMULT. Excel Magic Trick 1669.
HTML-код
- Опубликовано: 22 авг 2024
- Download Excel File: excelisfun.net...
Learn how to summarize Book Author Sales Units when there are multiple authors for each book and only Book ID in the Sales Table, not Author ID. Learn how to create OR Logical Tests in a number of different ways. Learn how to use FILTER, IF and MMULT to create the OR Logical Test Criteria inside the SUMIFS Function.
Topics:
1. (00:01) Introduction and Overview of logic for formula
2. (01:00) Formula #1: FILTER, SUMIFS, SUM and Direct Array OR Logical Test calculations.
3. (04:19) Formula #2: IF , SUMIFS, SUMPRODUCT and Direct Array OR Logical Test calculations.
4. (05:03) Formula #3: FILTER, MMULT, COLUMN, SUMIFS, SUM and Direct Array OR Logical Test calculations.
5. (05:39) Summary and End Video Links
You make complicated formulas seem so simple; excellent presentation. Also, thank you for showing the old school as it makes the transition easier.
That is my goal: make the complicated, less complicated. I agree with you Dennis D, for me too, I must always do both the New and Old School each time I make a formula because it make the transition to the new world easier!! It also has the added memory benefit that if you do the new and then do the old, the part in your brain that already encoded memory for the old, now gets encoding for the new : )
You will be liked as soon as you upload any video. Your viewers know whatever will come, will light the way for others.
Thanks for your kind words! It is fun for me to make and if it brings light to you and others, that is even better!! Thanks for stopping by in the comments, ripudaman!!!!
Amazing Mike "Excel guru" Garvin
Glad it is amazing for you, romeo!!!!
Not sure I don't prefer the "Old School" version (and in terms of clarity), but then I am old school. And MMULT just blew my mind. Thanks as always!
Wow Mike again with magic what man 👌👌👌👌👌👌👌👌
And very big thanx for your previous reply and can you also share with us excel chart series plzz do it for all of us
Thanks in advance
👍👍👍👌👌👌💐💐💐💐💐💐🎂🎂
You are rocking Mike
You are a real player of excel game
You are welcome, ashish!!! Thanks for your magic and rocking comments : )
I have a few epic videos about charts. Here are two:
ruclips.net/video/UVZcamKLJgU/видео.html
ruclips.net/video/xLmtGk7Ymy8/видео.html
Thanks Mike.... I love when Old School meets New School!
Awesome. The last trick is really tricky
Yes it is. Do check out the MMULT video, I explain all the beautiful complexities : )
Nice comparison how both old functions and new arrays are beautifully doing their job.
Hi Mike.. excellent! It's always fun to try and solve before looking at your solutions. Love it that you show both old and new and additional bonus formulas.. like MMULT. Lots of good learning in 6 entertaining minutes :)) Thanks for the weekend formula fun at ExcelIsFun! Thumbs up!!
You are welcome for the fun, Teammate : )
Amazing Mike, that 1st formula was just amazing
Yes, Double Or Logical Tests ARE Amazing!!! Thanks for stopping by, Chris : ) : ) : )
Great work Mike!
I see that the MMULT function is very powerful. Thank you!
es, MMULT is amazing. Be sure to check out the reference video : )
I love the filter function so much, thanks Mike
Mike, every time you bring a new video, there is definitely a new thing to learn, no matter how long we already have been using excel before. Thanks for sharing your expertise!
Regards,
Amardeep
AMEN !!! AMEN !!!!!
Always glad to bring new things to our Team, Amardeep!!!!
@@spilledgraphics Thanks, Teammate!!!
This is a very relevant video in my line of work. Thanks GM Mike!
Great. Thank you Mike. 👍 🌟 🌟 🌟
You are welocme, K D - thanks for the starts and thumbs ups : ) : )
Just wow... Speachless, you blew my mind...
Great formulas as always!
Excellent video sir. You are great.
Glad it is EXCELlnet for you, Ashok!!!!
Been looking for this info, great video!
Fantastic man!!!! Thanks 🇧🇷
You are welcome!!
You were having some fun with those Camtasia behaviors.
I love it!
Why not? Right, Teammate of Camtasia, Oz!!!!!!!
@@excelisfun It's BEATUIFUL and fun!
Thank you Mike!
Espectacular !!!💯
Glad you like it!!!
Fascinating challenge. Great solutions.
Oz Telling Tales, Rad!!!!! Power Query can make a Bridge Table for a Data Model Pivot Table too: ruclips.net/video/wcwV_RKOS5U/видео.html
Thanks Mike... wonderful as always! FILTER is amazing, but I would prefer to use the old school as I am working on different version (not 365) :)
Well, the Old School is easy too : )
Thanks Mike. Speechless as always!!!!
Yes!!!! Speechless is fun : )
Great video Mike!
Glad it is great, Anthony!!
Great video! Thanks Mike
You are welcome, Abdul!!!
Keep topping Mike ...
I will : )
Boom!Wow 3 Really Awesome Formulas..Thank You Mike :-)
Boom, Boom, Boom!!!!
I like all of your content thank you. The only thing that bothers me is the voice inflections. It is a bit distracting,
You r Fantastic.👍
Glad to help, Henrik!!!!
@@excelisfun glad that u share All this good knowlege to US ALL 😉💪
Thank you for this amazing video! Thumbs up!
Thanks a lot Mike, amazing video as usual.
i tried to mix up new and old school by doing these formula and it works
=MMULT(Sumifs($C$5:$C$22;$B$5:$B$22;INDEX($I$5:$I$10;AGGREGATE(15;6;ROW($I$5:$I$10)-ROW($I$4)/(($K$5:$K$10=E5)+($L$5:$L$10=E5)+($M$5:$M$10=E5));SEQUENCE(ROW($I$5:$I$10))))))
Thanks again :)
Luca
Great video again as usual. Get to learn a lot from your videos
Btw I am also passionate about excel and do have my own RUclips channel where I do try to make some videos that hopefully will help people..
Cheers
That's Cool ... thanks Mike
You are welcome, Hussein!!!
Fantastic video :)
Glad it is fantastic for you, Sachin : )
bonus number 2 : brain genius 🙂
no further search requested 👌
Fun with Excel ; )
@@excelisfun i think we d'ont get just fun with excel , but more becuase the work becomes fun with excel 🤣
very nice video sir
#microsoftmastertricks
OK, first off: nice main topic of the video, really good. (My gut feeling says I would use some nested VLOOKUP, but that could be wrong).
(BTW: I have this strange feeling of Déjà Vu, but this is a new, updated version.)
And now the rest:
- WOW! They should call you Mike “Speedy Gonzales” Girvin!! The way you churn out video’s like that!... :-)
- And at the end: BOOM! You kill it by referring to another video (OK, that’s not that ‘bad’), and then DOUBLE BOOM, you refer to an entire playlist of videos!!... :-)
- I’ve reached the level (about the hight of your ankles) where I’m seeing: Callouts, Arrows (different colors), Highlights, Sketch Motion,... ;-)
So much to learn!... :-)
Déjà Vu. You are right. I already did this exact example BEFORE Spilled Array Formulas:
MSPTDA 27: SUMPRODUCT & SUMIFS Worksheet Array Formula to Add Units in Many To Many Relationships, ruclips.net/video/D-lhoXK6WIg/видео.html
MSPTDA 28: Build Power Query Bridge Table in Power BI & Power Pivot for Many To Many Relationship, ruclips.net/video/wcwV_RKOS5U/видео.html
But last week while I was teaching this topic in my class, I relaized that with the new Excel Engine, there is a different way : )
So I had to make a video : ) : ) : ) : )
You are at my ankles. But I am at your ankles. The beauty of being human is that we are each, uniquely, unparalleled and with gifts to give to the world that no one else can give : ) But, I have been making videos for 15 years and you have been making videos for a few months, so probably I have some accumulated lessons that lead to efficiency. So much to learn, we both! It never stops, as along as we are doing something that we love : )
I'm new to this. There are so many playlist. Where to start from?
Needful, thanks for sharing ...
You are welcome : )
Every video of yours is very informative. Thanks again Mike.
I have a query where I have to prepare aging of Debtors location wise and consolidated ranging from 0-30, 30-45, 45-60, 60-90, more than 90 days. In this data, some parties having credit balance as well plus I have to merge or consolidate parties having debit balance at one location and credit balance at other location. Please help.
Try this great Excel question site: mrexcel.com/forum
Ini lebih baik daripada sumif, dengan pengelolaan data besar, menjadi lebih ringan
Nice
Hi, i make a comparative statement in my excel sheet there are more then 1000 columns and 15 rows. i want to add values from different cells when i use sum formula its gives me error that i am adding more functions. how can i sort it out.
Very Well done!!!
Can you help me??????
I have some data in Excel and the same data is Hyperlink with .doc files.
Sir here is my Question.
is it possible that when the mouse pointer over the data in specified cell (which is not hyperlinked) a comment or window popup with a Msg " NOT HYPERLINK " Just like window popup and show the hyperlink with address. I am waiting for your replay Sir....
I shall be very thankful to you.
Try this great Excel question site: mrexcel.com/forum
Wow.
Thanks, Phone Excel : ) : )
Can you please tell me how to convert a pdf file which is saved as pdf of an excel file and now I want to convert it again in excel file and get back the formulas. Please tell me how can I convert that pdf file into excel without losing the formulas.
Hi! Is there any solution of my query.. suppose I have a purchase order of size 6 and 7 is 100 and 200 respectively.. Iam going to receive these items from vendor , suppose in first shipment it gave 50 and 80 qty of 6 and 7 size respectively and so on. I want to color my PO if there qty exceed there limits.....plz help me..
This made me think of Power BI.. How would this be done in Power Bi considering the relationship between Books table & Authors table will only be made on 1 column (Author1-ID). So how can it detect the relationship in column Author2-ID as well ?
I already have a video about This Many - To - Many Relationship in Power BI: Power Query and Data Model solution here: ruclips.net/video/wcwV_RKOS5U/видео.html
@@excelisfun Lucky me...you even used the exact same data set :D. Great videos. Thanks
I was following along fine then Bonus Formula #2 hit the screen and my eyes misted over!? What? How? MMULT double negative and Transpose raised to the power of Zero. I entered another dimension.... When I have a clear brain moment I'll return to try and understand this! Is there another video which covers this?
I've not been aware of using boolean characters like * and + inside functions before. Has this always been available?
Always. Before SUMIFS and other new functions, it was mostly mandatory.
If this isn't ART, then what IS?
Thank you Mike.
I do have a Art Degree from SAIC (School of the Art Institute of Chicago). When I produce, edit and make, I do think of it as art : ) Thanks for noticing the art in it, Sam Sami!!!!
Suppose I have A data Table: In Column "A" ( A3 To A7") Contain Same Style NO, Column "B" ( B3 to B7 ) Contain 5 Different PO # ( Under the Same Style) & Column "C" (c3 to c7) Contain 5 different Qty for EAch PO (Under The Same Style ). I want "D3 to D7" Will Be Marged & Value Of C3 to c7 Will Be Summarized in this Marged Cell ,PLease Help
Aloweys amazing
Glad it is amazing for you, Michael!!!!
Some times when i use sumifs, it gives me Zero but if I use Sumif in the same sheet it works. Could you help me with this ?
I am not sure what the issue is : (
The issue is that it should return value not nothing
wow very goo but sir I hve excel version 2013 I hve no filters option so how cn do this formula 👍👍👍👍👍
The second formula works in any version. Did you watch the whole video?
Realized something, is no need at all for FILTER or MMULT in this case, SUMIFS criteria can handle arrays so, no need to make the criteria one dimension, check this out on G19
=SUM(SUMIFS($C$5:$C$22,$B$5:$B$22,IF(E5=$K$5:$M$10,$I$5:$I$10)))
Yes, I though I showed that formula in the video?
@@excelisfun Indeed , with OR (+) , no need of OR (+). Instead of if((col1=e5)+(col2=e5)+...n columns , witch creates a 1 column array for the sumifs criteria argument we can use if(e5=array), that delivers an array of 6x3 that can be used as criteria in sumifs and then sum(sumifs) delivers the result.
@@Excelambda Wow, Wow!!!!! That is beautiful : ) I totally missed that you put the full array in logical_test and value_if_true !?!?!? Beautiful solution. I have added it to the downloadable file. I miss stuff that people write in the comments often, but today I have an unfortunate reason: I crashed on my bike yesterday and was at hospital until midnight yesterday, so today I am a bit foggy...
Thanks for making the Team better with your cool contributions : )
💜
Thanks for the Lavender Heart, Lamees!!
Powerquery?
I already made a video about the Power Query and Data Model solution here: ruclips.net/video/wcwV_RKOS5U/видео.html
How can I use alternative formula in office 2019
Nope. Microsoft only made the New Excel Calculation Engine and Dynamic Arrays available in Office 365. Office 2019 is the first Disc Install Version that was dead on arrival. The day that 2019 was released, Office 365 already had the Beta Dynamic Arrays. There are so many things like Dynamic Arrays, LET Function, Power Pivot by default, Power Query automatic update and many other features that are so much better than any other version, that is makes it the ONLY version to have. The problem still exists that many entities, including the one I work for, have not switched to Office 365 and have technical barriers before they can switch. But in a year or two, all entities will have to strive to switch to Office 365, because it is just that good, and has that many efficiencies that make the switch worth it. The benefits of Office 365 out weigh virtually all the costs.
Try to listen this video and imagine Nicholas Cage is talking.
Does that help?
HOE TO SUMIF+FILTER+VLOOKUP
PLEASE SUGGEST