IF, FILTER, SUMIFS or COUNTIFS for OR Logical Test Aggregate Calculation? EMT 1746

Рет қаралды 14,913

Күн бұрын

Download Excel File: excelisfun.net/files/EMT1746....
Learn how to create an OR Logical Test to make an aggregate calculation depending on whether the test involves one column or two columns. Learn about the SUMIFS, FILTER and IF functions to help run an OR logical Test to make an aggregate calculation.
In this video:
1. (00:00) Introduction
2. (00:14) OR Logical Test Question on 1 Column
3. (00:26) OR Logical Test Question on 2 Columns
4. (00:46) SUMIFS and SUM functions to add sales where OR logical Test works on 1 column.
5. (02:03) SUM and FILTER functions to add sales where OR logical Test works on 2 columns. Microsoft 365 Excel formula.
6. (02:28) OR Logical Test with Plus Math Operator.
7. (03:05) what the 0s, 1s and 2s mean in an OR Logical Test.
8. (04:05) SUM and IF functions to add sales where OR logical Test works on 2 columns. Works in any version of Excel.
9. (05:17) Keystroke Ctrl + Shift + Enter.
10. (05:40) Why not use SUMPRODUCT?
11. (06:22) NOT Logical Test inside SUMIFS function.
12. (07:09) Universal formula for OR Logical Test in an Aggregate Calculation.
13. (07:35) Compare Two Lists Logical Construction to run OR Logical Test on 1 column.
14. (08:04) 1 Column Or Logical Test with Function Argument Array Operation works on the aggregate calculations: sum, count, min, max. It will not work on average.
15. (08:36) FILTER will work in any Aggregate Function, including AVERAGE and 2 column OR Logical Tests.
16. (08:45) Conclusion and Summary, Video Links and Outro

Пікірлер: 80

@ickyfilth2149 2 жыл бұрын

Thanks!

@excelisfun 2 жыл бұрын

Thanks for your generous donation, Icky Filth!!! It helps me to continue to make fun videos about the efficient ways to use Excel!!!

@anthonypasslow1933 2 жыл бұрын

I cannot get enough of the "BAM" from the narration. Great enthusiasm and communication skills

@excelisfun 3 жыл бұрын

This video is similar to the video I posted earlier in the month: Excel Magic Trick 1743. However, someone asked about the difference between a 1 column and 2 column OR Logical test. So this video explains that topic much better than the previous one and shows an easy old-school formula that uses the IF Function : ) P.S. The download workbook file has a few other solutions too, like SUMPRODUCT and DSUM.

@DavidGarcia-ph8rs 3 жыл бұрын

Many years subscribed to your channel, and every time you post a video, I learn something new. Thank you for all the amazing videos.

@excelisfun 3 жыл бұрын

You are welcome for it all, David!

@johnborg5419 3 жыл бұрын

Thanks Mike. Bonus Formulas were GREAT!!! I love them ALL!!! : ) : )

@excelisfun 3 жыл бұрын

Glad you like 'em, John Formula Guy Borg : )

@chrism9037 3 жыл бұрын

Bam! The master strikes again. Great video Mike!

@excelisfun 3 жыл бұрын

Even though it is very similar to EMT 1743 which I posted about three weeks ago lol BAM! Thanks, Chris : )

@chrism9037 3 жыл бұрын

That's ok, the repetition is helpful. I watch them all!

@wayneedmondson1065 3 жыл бұрын

Hi Mike. Awesome lesson.. as always! Here's another legacy formula that will work with a 2-column OR logical test: =SUMPRODUCT((((C5:C20=G7)+(D5:D20=G8))>0)*B5:B20). Fun to come up with different ways to solve the problem. Thanks for the Wednesday OR logical fun :)) Thumbs up!!

@excelisfun 3 жыл бұрын

Thanks, Wayne!!! Or, as I posted a few comments below, a slight variation: =SUMPRODUCT(B5:B20,--(((C5:C20=G7)+(D5:D20=G8))>0)). Go Team!

@wayneedmondson1065 3 жыл бұрын

@@excelisfun Thanks Mike! Go Team!!

@darrylmorgan 3 жыл бұрын

Boom!Super Cool Lesson With Really Wicked Array's Of Solutions...Thank You Mike :)

@excelisfun 3 жыл бұрын

I love this: Really Wicked Array's Of Solutions!!! Go Team!!!!!!

@HusseinKorish 3 жыл бұрын

Absolutely amazing Mike .... Just in the right time video ...you already answered two questions for me .

@excelisfun 3 жыл бұрын

I love it!!!!! You had questions, and I posted a video to answer it : ) : ) What were the 2 questions that you got answered?

@HusseinKorish 3 жыл бұрын

@@excelisfun 1st question was "how to use Filter for two columns criteria?" .... 2nd question was "why average ifs acting different than sumifs? "

@simfinso858 3 жыл бұрын

Oh 5 Bonus in 1 video that's amazing along with original amazing video.

@excelisfun 3 жыл бұрын

Yes, lots of bonuses is fun : ) : )

@gregorytaylor2749 3 жыл бұрын

Excellent tutorial!! Thanks!!

@excelisfun 3 жыл бұрын

You are welcome, Gregory!!!

@bamakaze 2 жыл бұрын

Hey Mike, thanks to you, I understand that "or" criteria in a single column will work with sumifs because the products are "mutually exclusive." Thank you Grandmaster Mike.

@SyedMuzammilMahasanShahi 3 жыл бұрын

That is so awesome. Thanks Mike for this EXCELlent video.

@excelisfun 3 жыл бұрын

You are welcome, Fellow Teacher : ) : ) : )

@deepakmirchandani1348 9 ай бұрын

great video sir, using Filter function and sum. thanks

@sat1460 3 жыл бұрын

Always waiting for your video

@excelisfun 3 жыл бұрын

Glad you like them, satish!!! I always can't wait to make new ones ; )

@josepedromunizvargas691 3 жыл бұрын

Wonderful! It is amazing excel lesson! Thanks a lot!

@excelisfun 3 жыл бұрын

You are welcome a lot, JOSE!!!!

@nadermounir8228 3 жыл бұрын

Great video Mike. Love the fact that you always mention the formulas to use in older versions of Excel. Absolutely amazing 👏.

@excelisfun 3 жыл бұрын

Yes and in the case, it is just as easy as FILTER , but you have to remember that special keystoke!

@mattschoular8844 3 жыл бұрын

Great examples again. Thanks Mike.

@excelisfun 3 жыл бұрын

You are welcome, Matt!!!! Yes, I have done these type of formulas before, thanks for watching again : )

@coogs9335 3 жыл бұрын

Fantastic..thanks again for posting MVP Mike....

@excelisfun 3 жыл бұрын

You are welcome for the posts, Pete!!!

@nimrodzik1 3 жыл бұрын

Great as usual :). Thanks Mike!

@excelisfun 3 жыл бұрын

You are welcome, nimrodzik : ) : )

@SantiagoMolero 3 жыл бұрын

Really interesting. Thank you!!

@JonathanExcels 3 жыл бұрын

You correctly anticipated my question about sumproduct. Thank you.

@excelisfun 3 жыл бұрын

You are welcome. Do you mean when I answered this way: Yes, SUMPRODUCT can do almost anything! But the logical test is a bit more complicated: OR Logical test: --(((C5:C20=G7)+(D5:D20=G8))>0) Full formula: =SUMPRODUCT(B5:B20,--(((C5:C20=G7)+(D5:D20=G8))>0)) ?

@JonathanExcels 3 жыл бұрын

@@excelisfun at 5:41 into the video.

@excelisfun 3 жыл бұрын

@@JonathanExcels, Oh, yah, the IF can go in SUMPRODUCT without Ctrl + Shift + Enter. I wrote about this strange anomaly back in 2012 in my Array Formula book : )

@excelisfun 3 жыл бұрын

I see teaching as being all about telling stories. Constructing a good story means anticipating questions and putting them into the story, up front. That is why as a teacher, every time and teach the same class again, it is different because I take the question that arose before and put them in the story.

@FRANKWHITE1996 3 жыл бұрын

Another great video! Thanks for sharing!!

@excelisfun 3 жыл бұрын

You are welcome for the share, long time viewer FRANKWHITE1996 : )

@FRANKWHITE1996 3 жыл бұрын

@@excelisfun 🙌🙌🙌

@CleverWoodenRhino 3 жыл бұрын

That f9 hotkey was new to me, do you have any video that goes over a list of these sorts of hidden but useful functions?

@ARH_POE 3 жыл бұрын

Neat video! @4:22 If you use =XOR() instead of =OR() it would only sum up the results that have a result of 1 right? The value that returned 2 would not be added?

@excelisfun 3 жыл бұрын

Yes. But those are scalar function which return a single answer and can not return an array like FILTER and IF need. But no worries, if you need the XOR logic, you can use: (C5:C20=G7)+(D5:D20=G8)=1, or the full: =SUM(FILTER(B5:B20,(C5:C20=G7)+(D5:D20=G8)=1))

@einoconsult5563 3 жыл бұрын

Mike ! Thanks for this. First formula you wrote 1:24 you took the two conditions in the first column G7:G8, what if I have one condition in G7 and the other one would be K23, what would be the syntax ? 😁

@excelisfun 3 жыл бұрын

Then you should use the FILTER or IF formula options.

@sachinrv1 2 жыл бұрын

Fantastic

@excelisfun 2 жыл бұрын

Glad you like it!

@cherylrb00 2 жыл бұрын

❤️ Help!!! If I am wanting to get a sum difference of two numbers then if it’s greater or less than 5 points am I able to do so? For example I have a spreadsheet with column one data and column two data. I then subtract the two columns to get a total but if it’s 5 more than the total or 5 less than the total I want to flag it in some way. Is this possible? I’ve only seen it done with percentages but I’m want the actual number. Thank you for all you do!! ❤️

@kartickchakraborty9135 3 жыл бұрын

Hi Sir, I was learning Power Query from your Tutorial (MSPTDA Series). I've just finished 8.5 Video (Group By). I must say, your tutorial is way way better than any paid course. But, one thing that I didn't like, that the absent of intelligence while writing Power Query Functions. Unlike, Excel and VBA, Power Query doesn't have this feature. On the top of this, Functions in Power Query are Case Sensitive. We, as a Beginners, find it very tough editing Formulas. Microsoft Should take this into consideration. 😭😭😭😭.

@excelisfun 3 жыл бұрын

I am glad that my videos an help. I am sorry that Microsoft makes things difficult for you : (

@man-nongjong3497 2 жыл бұрын

Thanks Sir.. How can I count the same values that occurred consecutively for ex. 5 times and sum them altogether. sample series of no.s 1,1,1,1,1,2,1,1,1,1,1,2,1,1 so count no.s of 1 five times consecutively which occurred 2 times. Really hoping for your response sir.

@anahitapouladi6829 3 жыл бұрын

Hi. I have bunch of little problems which really bugs me...I'll be really grateful if you help me with them. First of all, whenever you write a formula you would use(,) mark to separate each part but mine is different from you, how can I change it? And I have to write in Persian, unfortunately even though I change my language during typing formulas some times with large formulas it mix tow languages and mess every ting up. Any idea what to do to prevent this? Lastly for FV formulas when payment has put off for some years and don't know pmt, you'd put pmt cell empty and instead you'd used pv. But I can't do the same. It says the formula isn't correct and that I should fill pmt cell. Also in pmt formulas when I type either 1 or 0 again an error will pups up and says the formula isn't correct. But if I put that filed empty it goes without problem. I'm soooo grateful for your amazing videos, they'd thought me more than my university classes. I had once attended another excel class but although it wasn't free, wasn't nearly as good as yours. Thank you so much.

@excelisfun 3 жыл бұрын

The function argumnet settings are controlled in the Regional Settings in the Control Panel. I am not sure how to get your Persian Excel to use other types of Excel. I am not sure about your FV question. Try posting your question to this great Excel question site: mrexcel.com/board

@anahitapouladi6829 3 жыл бұрын

@@excelisfun Thanks for the first tow, and I'll check that website.