IF, FILTER, SUMIFS or COUNTIFS for OR Logical Test Aggregate Calculation? EMT 1746
HTML-код
- Опубликовано: 6 июл 2024
- 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
Thanks!
Thanks for your generous donation, Icky Filth!!! It helps me to continue to make fun videos about the efficient ways to use Excel!!!
I cannot get enough of the "BAM" from the narration. Great enthusiasm and communication skills
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.
Thanks Mike. Bonus Formulas were GREAT!!! I love them ALL!!! : ) : )
Glad you like 'em, John Formula Guy Borg : )
Bam! The master strikes again. Great video Mike!
Even though it is very similar to EMT 1743 which I posted about three weeks ago lol BAM! Thanks, Chris : )
That's ok, the repetition is helpful. I watch them all!
Many years subscribed to your channel, and every time you post a video, I learn something new.
Thank you for all the amazing videos.
You are welcome for it all, David!
Boom!Super Cool Lesson With Really Wicked Array's Of Solutions...Thank You Mike :)
I love this: Really Wicked Array's Of Solutions!!! Go Team!!!!!!
Absolutely amazing Mike .... Just in the right time video ...you already answered two questions for me .
I love it!!!!! You had questions, and I posted a video to answer it : ) : ) What were the 2 questions that you got answered?
@@excelisfun 1st question was "how to use Filter for two columns criteria?" .... 2nd question was "why average ifs acting different than sumifs? "
Oh 5 Bonus in 1 video that's amazing along with original amazing video.
Yes, lots of bonuses is fun : ) : )
Great video Mike. Love the fact that you always mention the formulas to use in older versions of Excel. Absolutely amazing 👏.
Yes and in the case, it is just as easy as FILTER , but you have to remember that special keystoke!
Excellent tutorial!! Thanks!!
You are welcome, Gregory!!!
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!!
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!
@@excelisfun Thanks Mike! Go Team!!
Great examples again. Thanks Mike.
You are welcome, Matt!!!! Yes, I have done these type of formulas before, thanks for watching again : )
Always waiting for your video
Glad you like them, satish!!! I always can't wait to make new ones ; )
That is so awesome. Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : ) : )
Wonderful! It is amazing excel lesson! Thanks a lot!
You are welcome a lot, JOSE!!!!
great video sir, using Filter function and sum. thanks
Fantastic..thanks again for posting MVP Mike....
You are welcome for the posts, Pete!!!
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.
Really interesting. Thank you!!
You correctly anticipated my question about sumproduct. Thank you.
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))
?
@@excelisfun at 5:41 into the video.
@@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 : )
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.
Great as usual :). Thanks Mike!
You are welcome, nimrodzik : ) : )
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?
Another great video! Thanks for sharing!!
You are welcome for the share, long time viewer FRANKWHITE1996 : )
@@excelisfun 🙌🙌🙌
❤️ 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!! ❤️
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?
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))
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.
Fantastic
Glad you like it!
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 ? 😁
Then you should use the FILTER or IF formula options.
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. 😭😭😭😭.
I am glad that my videos an help. I am sorry that Microsoft makes things difficult for you : (
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.
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
@@excelisfun Thanks for the first tow, and I'll check that website.
Row 9 is not included in the calculations when Carlota and Gigi are selected. Why is that? 860 is without the 50
Can you post Excel script
I do not understand what you are asking.
👍
👍🏻
Thanks for the thumbs up : )
First comment... 👍
You get the first place trophy!!!! Syed : )
Cadê os BR?
I hope you liked it, Loterias!!!!
@@excelisfun okay my brother
I liked very much
@@LoteriasSpaceDiamond Excel Brothers!!!!
: ) : )
I am so confused 😫