Top 6 Excel Counting Formulas: COUNTIFS, FILTER or SUM(IF) functions? Excel Magic Trick 1819
Вставка
- Опубліковано 13 лип 2024
- Download Excel File: excelisfun.net/files/EMT1819....
The best Excel formulas are not always what they seem. In this video: learn the best ways to create counting formulas in Excel using COUNTIFS, FILTER and SUM( IF ). Learn both Microsoft 365 Excel formulas and formulas that will work in any version of Excel.
Topics:
1. (00:00) Introduction
2. (00:14) COUNTIFS for counting customers who paid Cash. (Single Condition)
3. (01:07) AND Logical Tests. OR Logical Tests. And what is required in counting formulas.
4. (01:32) COUNTIFS for counting customers who spent less than $50 and paid with PayPal. (AND Test). Learn about two ways to enter comparative operator.
5. (03:18) OR Logical Tests and the trouble with them. How to not get in trouble.
6. (04:09) COUNTIFS & SUM functions for counting customers who paid with Visa or Mastercard. (OR Test, No overlap: Mutually Exclusive Categories))
7. (05:41) FILTER for customers who paid with Visa or spent at least $350. COUNTIFS alternative formula too. (OR Test, overlap in categories: categories are NOT Mutually Exclusive)
8. (09:45) SUM ( IF ) for customers who paid with Visa or spent at least $350. (NOT Mutually Exclusive)
9. (11:00) FILTER for customers who paid with PayPal or spent between$50 and $100. FILTER and COUNTIFS alternative formula too. AND Logical Test inside an OR Logical Test.
10. (12:32) Differentiate between the complex filters: 1) AND Logical Test inside an OR Logical Test, 2) OR Logical Test inside an AND Logical Test.
11. (12:48) COUNTIFS & SUM functions for customers who paid with PayPal or Apple and spent exactly $100. SUM ( IF ) alternative formula too. OR Logical Test inside an AND Logical Test.
12. (14:43) Summary of all counting methods
13. (15:48) Closing & Video Links
#excel
#excelisfun
#excelformulas
#excelfunctions
#dynamicarrays
#array #arrayformula
#statistics
#excelsolutions
#joint
#probability
#probabilitytricks
#mikegirvin
#analytics
#analysis
#dataanalysis
#dataanalytics
#excelmvp
#countifs
#filterfunction
#iffunction
#ifformula
#logicaltest
#logicalfunction
The most amazing thing in "ExcelIsFun" channel is that Mike never quit explaining the basics ...how to highlight a column in a table... how to evaluate a varaible in a formula .. so no one ever interrupted through the video or ask "how is that happened ?" ... Thanks alot Mike.
Thank you for being so perceptive, Hussein and noticing what I do : ) : ) : ) You got it right: that is 100% intentional. From the very beginning 15 years ago. It makes the process of creating videos more difficult, because I always feel that I cannot make assumptions - so I always have to include all the detail. But that way, no one is left out, even us not so smart people (like me)!!!!
You are welcome a lot, Hussein!!!
Exactly why I followed you Mike for that long :) Reinforcing the basics is good even for advanced people bc to become an expert you always need to understand basics
@@Sal_A Me too. I forget my basics when I do not use them for a while... AND: I have to come back and watch my own videos to re-remember lol
Dear Professor Mike, I just wanted to express my gratitude for your excellent tutorial. Your videos, tips, and much more have helped me gain a deeper appreciation and love for Excel. 👍👍👍👍
I love to hear that!!! Anytime I can increase the love of Excel, I have accomplished part of my goal : ) : )
Never cease to be amazed by you Mike. Wonderful stuff. Many thanks.
Many you are welcomes, Sonic Cheese!!!!
Boom!Super Fun Class.Really Like The ROWS and FILTER Method...Thank You Mike :)
Cool: Vote for ROWS and FILTER : ) That is the one I use to teach students in my statistics classes because it is an easy visual way to see the records that match the criteria.
Thank you Mike for this EXCELlent video.
You are welcome, Fellow Teacher Syed Muzammil Mahasan Shahi!!!
Just last week i was breaking my head on non mutually exclusive counting. Thank you for making this so simple
Yes!!!! I love to hear that I can help with that. I absolutely remember in the beginning of my career pulling my hair out with it... You are welcome for the simple!!!
Thanks so much for all these videos Mike. I think you've done more for educating people in Excel, than all of Microsoft combined. Thanks again.
I try to provide education to all!!! You are so welcome for the vids!!!!!
Finally, I can see the face behind all your magnificent videos...Thx
You are welcome, Roman!!!!!
Thank you Mr Bam! Very useful. 👍
You are welcome!!!! Sincerely, Mr Bam ; )
Mike showers us with golden nuggets if wisdom!
Always good stuff and thanks for all the time you put into editing your videos.
I don't know how you're able to consistently produce such great content - enjoyable and educational.
Keep up the great work!!! Thanks Mr. Girvin!
You are welcome for the nuggets!!!!!
How do I do it? If you look at my production time log: you will see many days between 10 and 13 hours. It is grueling sometimes, but always worth it!!!
Stellar Video Mike 📹 👌 Thank you for your very hard work 👍
You are welcome for the stellar!!!!
You are a magician. I like the Filter method. That was neat!
Yes!!!! Glad you like the magic and FILTER!!!!
i consider very good in making formulas in excel but always i found new things to learn from your videos so thanks
You are absolutely welcome!!!!! It is so true: even one new nugget in the Excel toolbox is awesome!!! : ) You are welcome as always, Sevagjb!!!!
@@excelisfun I'm using the 365 version and i enjoy the new personal toolbar ,
it's The best addition value to use the 365
@@sevagjb Wow, that's cool! So are you saying that the QAT, where you can add any buttons you want, is what you like?
@@excelisfun yes, but now it appears as independent toolbar and hid all the rest,
I'm on the beta channel, the new name is Personal Toolbar
@@sevagjb Hid all the Ribbons? So are you saying you just have a personal toolbar? I have never seen that before.
Thank you Mike for the great video. I liked the filter function with rows, but dang things can get complicated when you have to avoid double counting. Hey I'm making it through your book THE ONLY APP THAT MATTERS; I'm taking it slow, doing a chapter, setting it aside for awhile, doing another chapter later. I'm really enjoying it Mike; great job!
I am so glad that you like the book. This video comes straight from chapter 13!!! By The Way, OR can be easy if you just use (Column=condition)+(Column=Condition) in FILTER or SUM ( IF ) because that construction works for both mutually exclusive and not mutually exclusive : ) : )
Did you buy book at Amazon?
A great demo. Thanks!
You are welcome!
Awesome Mike! Always something new to learn from your EMTs. Thumbs up!!
Whatdaya learn this time, Wayne ?
@@excelisfun Reminder of ME/NOT ME and OR(AND) and AND(OR). Also, to use good old IF function. Good stuff!!
@@wayneedmondson1065 What is ME/NOT ME??? I even forget to use SUM ( IF ) often too. I always use it for averaging (because you can not use AVERAGEIFS for OR), but I often forget that it is actually a shorter formulas that any other! Good Old IF is good stuff!!!
@@excelisfun ME = mutually exclusive from your video. Agreed on IF!!
@@wayneedmondson1065 Yes!!!!!!!!
Thank you Mike for teaching these valuable tricks 🙂
You are welcome, Excel Ghost Teammate !!!!!
Versatile and an excellent refresher with a "filter" :)
Thanks!
You are welcome, Viktor!!!
Thank you for explaining what 'Function argument array operation' is. I've been wondering what that means!
You are welcome!!!!
Thank you Mike. Outstanding as always. Regardless of simple or complex content, you make it understandable and FUN!
I am glad that my stories are fun and informative : ) You are welcome!!
Thanks Mike! I use SUMIFS but I have been using FILTER so much more now!!
FILTER definitely opens things up and can make some things much less complicated. You are welcome, Chris M!!!!
Your tutorials are *great*
I really appreciate all your highly recommended videos.
Salim
You are welcome, Salim!!!!!
That SUM+IF combo is really great!
Yet, "lifting" SUMIFS (or 'vectorizing' as I like to call it) is very good technique, as well.
Cool video, thanks Mike! 🙂
Right: Use SUM ( IF ) when you can't use the mouth-full: "function argument array operation" (more descriptive for us non-engineers than the succinct "vectoring") SUMIFS/COUNTIFS and the like : ) : )
You are welcome, Teammate The Exceleer!!!
@ExcelIsFun "Vectorizing". Yes, engineering, but also shades of SUMPRODUCT!!
@@richardhay645 Isn't SUMPRODUCT an engineer? lol
@ExcelIsFun Apparently so and VVERY LOL that you would say that! In about 2000 when Excel was far more limited and my knowledge of Excel even more so, I complained to a business aquaintance who was a far more advanced user than I AND WHO WAS A STRUCTURAL ENGINEER that I wished that Excel could do operations that were more than cell by cell computations. So he mentioned SUMPRODUCT and spent about 10 minutes showing me a couple of things it could do. I thought that it was a game changer! I began to view my data sets as Vector Spaces from that moment on. Until about 2010 when I began to take Excel much more seriously SUMPRODUCT was my favorite and most overused Excel function.
@@richardhay645 LOVE it!!!!!!
Mike, you are an outstanding teacher
Thank you for your kind words : ) : ) : )
Amazing, Mike. Thank you!!!
You are welcome, Santiago!!!
Great video, Thanks 😊
You are welcome!!!
thanks you
Thanks You mr Mike 👍🏻
You are welcome, Carlos!!!!
Amazing content and explanations Mike.
Glad you like it, Mark!!!!
Very informative video Mike👍
Glad it helps!!!
You are Amazing sir 😘
Always entertaining with new new new....... Always new.
Dubble click and send it down 😅😂
If only the rest of our working tasks were as easy as Double Click And Send It Down lol
Thank you sir🙏🏻🙏🏻
You are welcome!!!!
Hi Professor, just one remark to your awesome video. I think you have left out the dearest SUMPRODUCT function which can also do the same with a very straight forward logic behind.
just awesome sir
Yes!!! Glad you like it : ) : )
GREAT tutorial. Thank you Professor!!! 👍👍👍
You are welcome, Kevin!!! But from the many, many videos that you have watched over the years, this is probably a review for you, right?
@@excelisfun I don’t follow the question, but I do know that lately I’ve been struggling with these formulas and this is a great reminder to me that I can use FILTER with ROWS. For some reason I have an easier time understanding the FILTER function and how it operates. I don’t think it’s a matter of the best solution, but rather the solution we can wrap our heads around, if that makes any sense… 😂 You always give us options, and we can choose the one we can make work. 👍
Great summary! Never use COUNTIF nor SUMIF.
I 100% agree: Never use COUNTIF nor SUMIF... But wait... There is a killer use for SUMIF when you use just the first two arguments (a bit of Microsoft coding from WAY back in the day):
1) If you enter criteria range as a range, then criteria, you only have to list the first cell in the sum range because it can infer the size from the criteria range, like: =SUMIF(C5:C13,F8,B5)
2) If the criteria range and sum range are the same (like for numbers and a hurdle, then you do not have to put sum range in formula, like: =SUMIF(B5:B13,">="&F11)
I teach just like you do: Always use S versions of SUMIFS and COUNTIFS because 1) they are more flexible in that they allow one or more conditions AND 2) the function arguments in the screen tips are much easier to interpret.
Thanks for the idea for the next EMT, Richard Hay!!!
@ExcelIsFun Yes that is true and I have been aware of that "trick" for years (without knowing how I learned it). BUT i virtually always have my data sets in Tables so the range is just a click away (that "black arrow," thing LOL) and the structured refs make the fomulas very easy read. I do realize that most users do not routinely maintain their Data Sets as Excel Tables and would find that trick useful!! Great idea for an EMT!
@@richardhay645 Not my idea: You get all the credit : ) : ) : ) : ) We still should only use SUMIFS and COUNTIFS, but maybe we should have those two party tricks up our sleeve?
@ExcelIsFun ìt is valuable for many users. Many stll use the IF forms for single criteria. For those many users who do not maintains their fact tables as Excel Tables a common source of error is mis-highlighting that creates ranges of different dimensions. This trick helps avoid that error. Users who tend to make that error are often those who will not quickly realize the source of that error. So this trick can actually be useful. But for me, I'll stick with IFS!!
@@richardhay645 Me too IFS Rule!!!!!!
Thanks a lot for a high level summary 🙏
You are welcome, Vijay!!!!! I did not view this as a high level summary (if high level summary means overview with less detail), but more as a deep dive about details, right?
@@excelisfun Thank you for correcting me 🙏
@@vijayraghavanr1 lol, did you mean: Low Level Summary ; )
@@excelisfun No. I meant deep dive
@@vijayraghavanr1 Oooo, I understand now. Semantics lol Dep Dives are fun!!!!
Super vid, Mike! I always wondered why MS programmed the criteria arguments to require the quotes and the ampersand. I really wish they'd get rid of it.
I think it has to do with the comparative operator in a cell with a number is text.
Thank you. I got a new favorite now. =Sum(if(conditions,,, I have used rows(filter(conditions). The problem with Filter here is that i returns 1 even if no conditions is met. Since it either return an error, or the value in If "If empty" option.
Very good!!!!
For the OR logical test, you can use an ifs function and count. This is faster since you only check rows that haven't already had a True condition.
=COUNT(IFS(S[PayMethod]="visa",1,S[Amount]>=350,1))
Thanks for the cool formula! The COUNT and IFS is comparable to other SUM and IF constructions, but it does appear to be a few milliseconds faster when you measure it on big data. However, COUNTIFS is significantly faster. I wrote about this phenomenon back in 2012 in my Ctrl + Shit + Enter book.
However, this is not correct: "It is also faster since you only check rows that don't have a true condition yet."
Direct array operations, like = and >= always check all rows. This is a hallmark of array calculations and it is is one of the reason that direct array operations are often slow as compared to worksheet functions like COUNTIFS and SUMIFS, or DAX Functions. Using Excel MVP Charles Williams's Fast Excel Timing Tools, when I time formulas I get this speed ranking:
These are times on a large data set with over 500,000 rows:
Average of 3 times in milliseconds = 76.92 for this formula: =COUNTIFS(S_2[PayMethod],F5)+COUNTIFS(S_2[Amount],">="&G5)-COUNTIFS(S_2[PayMethod],F5,S_2[Amount],">="&G5)
Average of 3 times in milliseconds = 139.23 for this formula: =COUNT(IFS(S_2[PayMethod]="visa",1,S_2[Amount]>=350,1))
Average of 3 times in milliseconds = 143.2 for this formula: =SUM(IF((S_2[PayMethod]=F5)+(S_2[Amount]>=G5),1))
Average of 3 times in milliseconds = 145.6 for this formula: =SUM(IF(S_2[PayMethod]=F5,1,IF(S_2[Amount]>=G5,1,0)))
Average of 3 times in milliseconds = 152.87 for this formula: =ROWS(FILTER(S_2,(S_2[PayMethod]=F5)+(S_2[Amount]>=G5)))
Hello sir, please show us your complete setup, I am your youtube member since 2019, please show your complete setup Thankyou .
What do you mean setup?
Well explained, as always. I’m now a FILTER guy, but still use SUM(IF(…)) now and then.
That is a good assessment. FILTER is so nice becasue you want "see" the matching records. But SUM ( IF ) is the shortest formula - so compact and nice.
@@excelisfun I often use FILTER in a LET function when the formula is long or needs explanations: the possibility to define variables for ranges, conditions, etc was a game changer. Formula auditing can be done by temporarily changing what the LET function returns (e.g. the list of True/False).
@@TSSC TRUTH: FILTER in a LET function when the formula is long or needs explanations = Awesome!!!!
@@excelisfun And the possibility to insert “comments”. Example:
=LET(
__Block_1, “Define variables”,
…
__Block_2, “Filter and calculate”,
…
__Block_3, “Stack columns to create a table”,
…)
@@TSSC Way cool!!!!
Gotta adapt to arrays 😲
Yes, indeed!!!!!
Magic functions ! Can this practice be used on version 2019. Thanks
COUNTIFS and SUM (IF ) can be used in Excel 2019.
Do you have video that has , multiple sender and multiple Receiver ? What is tha fornula
Beautifully and Plain explanation of each of the thought process and steps. Thank you so much.
I've a question, how you put the images in comment or Notes?
I should make a video about that... However, I have a crazy video about how to do it from back in the beginning of UA-cam when most videos were crazy, and I tried to fit in with crazy vids: ua-cam.com/video/hdTnZwgB_Q0/v-deo.html Mt Irian was a famous UA-camr back in 2008.
I should make a video about it, because almost no one uses comments any more and virtually no one knows the trick of pictures in comments. Maybe a few EMT ahead : )
@@excelisfun Yeah, that would be very helpful.
I find it very useful, It can be used as tooltip.
@@nishantkumar9570, The steps are: 1) Shift + F2 to Insert a Comment, 2) then carefully click outside edge of comment, 3) Colors and Lines tab, 4) Fill Color dropdown, 5) Fill Effects, 6) Picture tab, 7) Select Picture. This is just about the most difficult trick in Excel.
@@excelisfun Please do the honor of simplifying and demystifying this trick as well, like you always do.
Hi Mike, I'm curious, why rows (filter) and not count(filter)? Is it faster or handles errors better? Or just personal preference (I don't think I ever used rows till now 😜 )
COUNT counts numbers, so that would not work here. COUNTA counts not empty cells, and that is what most people use. If you actually have records, like I have, then COUNTA would double count. To fix that you could just use a single column to filter. But I like ROWS because it is exactly specific in that it counts rows. It is just preference, although I did hear Mr Excel (Bill Jelen) once say that it is faster becsue it just looks at last and firts and subtracts, rather than count all elements.
@ExcelIsFun To quote Dr Phil "Ok, I'm confused"!! I didn't download the worksheet but the output of FILTER appears to be a matrix 82x2 (rxc). One col is Text and one col contains Numbers.
Then COUNT(FILTER)=82. COUNTA(FILTER)=164. COUNTA(FILTER)÷COLUMNS(FILTER)=82. ROWS(FILTER)=ROW86-ROW5+1=82.
BUT this case is unique since only one column contains numbers. In the general case multiple colums could contain numbers. In such situations, it's COUNTA(FILTER)÷COLUMNS(FILTER) or ROWS(FILTER). ROWS is obviously easier but so long as you know without guessin or counting the number of colums the COLUMNS(FILTER) would be a hard coded scalar (like 12) so either formula works.
I think the general reluctance/resistance to ROWS. is its bad name. I have always belived that the MS executive who makes the six (or seven!) figure salary to name functiond and features in Excel was probably the inspiration for the old county hit song "A Boy Named Sue". Badly named functions have to fight for love and respect. ROWS should be CCOUNTROWS; ROW: ROWNUM: COUNTA:COUNT; COUNT:NUMCOUNT!! OFFSET and INDIRECT (seriously) ?????. The really crazy thing is that COUNT on the worksheet Status Bar is actually COUNTA and on an Excel Table the function dropdown on the Total Row which is actually the SUBTOTAL FUNCTION (someone at MS thought that to be a good idea!) contains COUNT NUMERS for COUNT and COUNT for COUNTA (yet ANOTHER "Boy Named Sue" moment!!).Maby that has added to the caution regarding the Reluctance (or maybe Rowluctance?) for ROWS!
@@richardhay645 Great commentary, Richard!!!! Jonny Cash: "A Boy Named Sue" is just great : ) : ) : )
can you please tell how to update my excel version 2019 to 2021?
Why do COUNTIF function gives #VALUE when used in a let function especially in cases where the criteria is a list of values.
I do resort to use map function to do that with SUM.
Probably from the horribly famous fact that Microsoft programmed the range arguments in COUNTIFS/SUMIFS/COUNTIF and others to not accept array operations. I have written about this in all my books and have many videos about this unfortunate fact. However, I am not sure what you mean when you write: "especially in cases where the criteria is a list of values", because the criteria argument can do function argument array operations, but the range argument can not. I cannot fully understand your question because you said COUNTIFS in LET, and that is almost certainly not what is causing the error. More than likely the cause of the problem is this historical fact about COUNTIFS and the like...
No Idea you could just put an array into countifs, I've always used the curly brackets and then type in the criteria;
Countifs( range, { "A,", "B" } ), also how about using ;
COUNTIFS(S_3[Amount],"="&G18,S_3[PayMethod],""&F18)+COUNTIF(S_3[PayMethod],F18)
NOT!!!!! : ) lol Thanks for the not tip, William : )
Is it possible to learn excel and Word on office version 2007 or new version is required?
That is a REALLY old version. But Here is a class I made back in 2013 that will work for Office 2007: ua-cam.com/video/OABx4yQZiP4/v-deo.html
@@excelisfun thanku sir
@@ashulalgarhia You are welcome! Just keep clicking that thumbs up - that is the fee I charge for the class lol