It may be dark and raining outside, but it always feels like a bright sunny day when I can start it off with a Prof. Leilia video. Once again, shining a light on a topic I would not have noticed. A very pleasant surprise ... thank you ... thank you ... thank you ... 😍😍😍
Ah thank you. I posted that video and then went off on vacation. Now I’m back and working on the next one for the coming week. Hope you’ve had a good summer so far.
The last 5 - 6 years excel improved a lot. I am doing so much with dynamic arrays today and was able to replace less efficient ways and VBA in a lot of occasions.
Thanks for explaining this. You explain these things so well that you open up a lot of possibilities for many of us who would never be able to figure these out on our own.
One issue with CountIfs is that while: =COUNTIFS(C3:C21,"") will count both an empty cell and "" resulting from a formula. =COUNTIFS(C3:C21,"") which is intended to be the opposite and exclude both will in fact include "" So although you would expect the sum of the 2 being count of empty cells + count of non empty cells = total count, it will not be because the "" cells will be double counted. You will need to use SumProduct or Sum (MS365 or 2021) or the filter function in the scenario.
You can try to count a value that you know is not in the range, for instance: COUNTIFS(C3:C21, "{{{{{{"), and this will count all the cells, no matter if they are empty, "", or with any text.
@@LaloinLondon That counts every cell regardless of content. One work around is =COUNTIFS(C3:C23,"?*") but it will ignore numbers. Sumproduct & Filter definitely work.
I think you must be psychic - I was trying to do this a few hours ago and didn't know that such a function existed! I was sorting the columns and looking at the row numbers to calculate the numbers involved, but my file was geophysical data, so thousands of rows not just a few - and my version is Home & Student 2016. Thank you Leila, as usual you come to the rescue!!
Hi Leila, your method for COUNTIFS OR only works in when both criteria ranges are the same otherwise you have potential for double counting. If you wanted OR criteria for multiple ranges you would need to subtract the intersection (AND) between the criteria which gets complicated if there is lots of criteria. Instead I would recommend using the same syntax as the "include" argument of the FILTER function and then using the SIGN function to only get 1s and 0s then take the SUM
I enjoy countifs, and use it in a biweekly report to management. Thank you for this super tutorial on so many uses, and I can surely add these use cases to my repertoire ❤. Thank you again, and love your very becoming glasses - I'm still on the hunt for good ones!
Very timely and helpful! Syntax for this function (and its cousins) is non-intuitive; I would NOT have figured out all these differences on my own. Thanks!!
Leila, Please note that the wild card is called an "aster isk" not an "aster ix". Asterix the Gaul was a Frenchman in cartoons. Sorry for the comment but you are not the only one in Excel who says this. I would also like to say that your videos are very helpful and so clearly explained. I watch almost every one of them. Please keep up your good advice. Thank you!
You're right David. But there's plenty of native english speakers who pronounce the "sk" as "ks" like an "x". Examples: 1. asterisk = asteriks 2. Ask = aks (both long and short A usage)
Haha thanks noted. Asterix and Obelix my favorite cartoons - actually I had most of their comic books. But not as many as Tintin. I had every single Tintin comic 😊. good times.
Thank you for solving a nerving problem for me with your short solution for an OR formula! 🙏😎 Does ist also work if I have to enter hardcoded specific text in "" instead of self-reference?
Thanks so much for this. I didn't even realize my Excel had a COUNTIFS function. I've always used various combinations of COUNTIF, sometimes having to move data to other cells and/or worksheets to do what I need.
A very usedul function, I've now incorporated it on one of my spreadsheets! Another well-presented tutorial, clear and understandable, without dragging it out like many other instructional videos do 😃👍
In some cases, although the cells appear empty, there may be formulas that hide the value in their content. For this reason, the count value of empty cells may give the wrong value. Your suggestion on this issue? Another issue is that "" is used to show different values in cells. Should ">
I also use the Countifs as a makeshift "rankifs" formula. Biggest to smallest rank: =countifs(numbersrange, ">="&target number, criteriarange1, criteria1...)+1
I can't believe your doing a video on this. I tried using Countifs last week and tried to do the "greater than" example you did but I didn't have quotation marks and I couldn't get it to work! Amazing! Thanks so much! And thanks Google for listening to my conversations and suggesting the video. 😂
Thanks Leila. Often counting blak/nonblanks cells that contains a formula is a pain, maybe you can give/explain the best solutions for that. Example: see the chapter where you explain the count blanks/non blanks, the given solution doesn’t work if there is a formula in column C [country].
Ms Gharani, as usual, a fascinating exploration of the COUNTIFS function. Think the most important thing I took away from this video is that I need reading glasses as well. Even after squinting as hard as possible, I could not read the inscription on your Award for Excellence. Congratulations. Always a wonderful delight when you post a video. Think you are HOT with or without the reading glasses. You have been missed. God Bless you and those you care about.
Maybe "Power Toys" could be a next topic for you, Leila. I was looking for ways to use the color picker in MS Word. Eventually, I installed Power Toys. The Color Picker "add-in" works system wide. Power Toys has more to offer than the color picker, alone...
Oh I love Power Toys. Use it all the time. Color picker is great. And so is fancy zones. That's the one I use daily actually. I made a video on that: ruclips.net/video/ZlyxesiiC6U/видео.htmlsi=QXqgpjZHWb-X0qts
Countif and countifs have equal arguments order, but sumif and sumifs not. So in the last case we need to prefer use multiple criteria as common case. Also, thanks for sum tip with OR criterias. :)
Hi Leilia, I follow up your YT Chanel years algo and now I feel so confidence with use Excel functions for get AND extrac information. But now a new difficultis apper, May I want to asking you if you can change your videos for show us how present the data with grapics
It may be dark and raining outside, but it always feels like a bright sunny day when I can start it off with a Prof. Leilia video. Once again, shining a light on a topic I would not have noticed.
A very pleasant surprise ... thank you ... thank you ... thank you ... 😍😍😍
Ah thank you. I posted that video and then went off on vacation. Now I’m back and working on the next one for the coming week. Hope you’ve had a good summer so far.
I just realized that newer versions of excel don't require you to do anything special for arrays, I'm very happy that I upgraded!
The last 5 - 6 years excel improved a lot. I am doing so much with dynamic arrays today and was able to replace less efficient ways and VBA in a lot of occasions.
As always, a gem of a tutorial! I've played with COUNTIFS but this explains what a couple hours of playing MIGHT have. Thanks, Leila!!
Great video as always! If you want to count cells that contain text you can use ">
Thanks!
Hi thank for this trip.
Beautiful. Short, concise, precise 👏 I'll put this video in my newsletter, let it spread!
Thanks for explaining this. You explain these things so well that you open up a lot of possibilities for many of us who would never be able to figure these out on our own.
A very timely tutorial for me. Many thanks
I love your knowledge ❤You look amazing with your glasses.
I was thinking it, but didn't want to say it 🙂
I pronounce this formula "Count IFs" like the ifs and the whys.
WOW the notification message ... very creative.
One issue with CountIfs is that while:
=COUNTIFS(C3:C21,"") will count both an empty cell and "" resulting from a formula.
=COUNTIFS(C3:C21,"") which is intended to be the opposite and exclude both will in fact include ""
So although you would expect the sum of the 2 being count of empty cells + count of non empty cells = total count, it will not be because the "" cells will be double counted.
You will need to use SumProduct or Sum (MS365 or 2021) or the filter function in the scenario.
You can try to count a value that you know is not in the range, for instance: COUNTIFS(C3:C21, "{{{{{{"), and this will count all the cells, no matter if they are empty, "", or with any text.
@@LaloinLondon
That counts every cell regardless of content. One work around is =COUNTIFS(C3:C23,"?*") but it will ignore numbers. Sumproduct & Filter definitely work.
Thank you! This got me sorted on the Sumifs function.
😁
I think you must be psychic - I was trying to do this a few hours ago and didn't know that such a function existed! I was sorting the columns and looking at the row numbers to calculate the numbers involved, but my file was geophysical data, so thousands of rows not just a few - and my version is Home & Student 2016. Thank you Leila, as usual you come to the rescue!!
Could have nested the "and" function.
Hi Leila, your method for COUNTIFS OR only works in when both criteria ranges are the same otherwise you have potential for double counting. If you wanted OR criteria for multiple ranges you would need to subtract the intersection (AND) between the criteria which gets complicated if there is lots of criteria. Instead I would recommend using the same syntax as the "include" argument of the FILTER function and then using the SIGN function to only get 1s and 0s then take the SUM
Use count instead of sum(sign). Well counta maybe :)
Great video Leila, the wildcard part was very cool
that was a new one for me too. I use wildcards in other situations, but I newer got the idea to use them in EXCEL.
You are amazing at explaining
I enjoy countifs, and use it in a biweekly report to management. Thank you for this super tutorial on so many uses, and I can surely add these use cases to my repertoire ❤. Thank you again, and love your very becoming glasses - I'm still on the hunt for good ones!
Very timely and helpful! Syntax for this function (and its cousins) is non-intuitive; I would NOT have figured out all these differences on my own. Thanks!!
You do such a great job explaining and expanding on concepts. Great Stuff.
Leila, Please note that the wild card is called an "aster isk" not an "aster ix". Asterix the Gaul was a Frenchman in cartoons. Sorry for the comment but you are not the only one in Excel who says this. I would also like to say that your videos are very helpful and so clearly explained. I watch almost every one of them. Please keep up your good advice. Thank you!
tōmā-tō vs tōma-tō
I say star you say ask her rick.
You're right David. But there's plenty of native english speakers who pronounce the "sk" as "ks" like an "x".
Examples:
1. asterisk = asteriks
2. Ask = aks (both long and short A usage)
Haha thanks noted. Asterix and Obelix my favorite cartoons - actually I had most of their comic books. But not as many as Tintin. I had every single Tintin comic 😊. good times.
My favorite short cut now is Ctrl +D. It's so cool.
Great comprehensive video! Btw, if you want to count values that are smaller or greater than a certain number you can type < (or ,
Thank you for solving a nerving problem for me with your short solution for an OR formula! 🙏😎
Does ist also work if I have to enter hardcoded specific text in "" instead of self-reference?
مرسی لیلا خانم😘😘
As always, thank you for your care and professionalism.
நன்றி அம்மா,
இதேபோல் sumifs function-க்கும் உபயோகப்படுத்த இயலுகிறது.
That was very useful. I have used the countifs a lot but did not know about the wild card function. I will definitely be using the OR function
Thanks so much for this. I didn't even realize my Excel had a COUNTIFS function. I've always used various combinations of COUNTIF, sometimes having to move data to other cells and/or worksheets to do what I need.
Thanks
You are a beautiful soul.
I would never had figure out you need to put the operators in quotes!
Thank you!!!😊 you were missed!
You are so welcome. Thank you for dropping by.
I think we should revise everything.
Thankyou 😊
A very usedul function, I've now incorporated it on one of my spreadsheets! Another well-presented tutorial, clear and understandable, without dragging it out like many other instructional videos do 😃👍
Waiting for your tutorials always. Reading glass are alright.
Thank you, excellent video, your tutorials have helped me so much particularly excel filter which has increased processing time exponentially.
Absolutely helpful as always!!!
Great video Leila
Distinctive as usual ، thanks for sharing .
The message pop up to subscribe the channel is very creative. I liked it very much.
Thanks so much. It is very helpful and easier to understand
In some cases, although the cells appear empty, there may be formulas that hide the value in their content. For this reason, the count value of empty cells may give the wrong value. Your suggestion on this issue?
Another issue is that "" is used to show different values in cells. Should ">
I've used countifs but never knew all the options. Thanks for enlightening me!!! BTW, the glasses make you look smarter, if that's possible 😊
I also use the Countifs as a makeshift "rankifs" formula.
Biggest to smallest rank:
=countifs(numbersrange, ">="&target number, criteriarange1, criteria1...)+1
Fantastic function, fantastic explanation, thank you .
Impressive as always. Thank you Leila
Thank you! Cheers!
Useful and informative as always.
Sepaas.
This is a really helpful tutorial Leila - thank you so much.
I can't believe your doing a video on this. I tried using Countifs last week and tried to do the "greater than" example you did but I didn't have quotation marks and I couldn't get it to work! Amazing! Thanks so much! And thanks Google for listening to my conversations and suggesting the video. 😂
Thanks for this video which is very helpful. And don't worry about your glasses (which suits you very well!)
Thanks for the tips❤ very helpful
You look awesome...thanks
Great video Leila!
Great video, thank you!
Very informative tutorial, thank you as always 😊. You look good in glasses btw. 😁
Awesome value addition
But I have 1 query - in arrayfilter functions the lookup functions don't work
Great video, I think you shold create a full course on ower automate, there are many possibilities to automate manual work to save time.
Super helpful. Thanks
Excellent
❤
Really workful topic ❤
very helpful.. your explanation is beautiful like U
Excellent video on countif madam 🙏
Welcome back Leila
Really liked this as I have used and struggled with this function in the past
Great info ... and as always the presentation is spot on. .... BTW ... love the glasses .. best regards from chicago
great. Thank you!
Absolutely fantastic. Thank you.
Thank you
You always good, start from simple and break the complex
this was extremely useful & simply explained, thanks
Brilliant! Thanks Leila
Awesome as always. Thank you.
Your simply the best! :)
Thanks Leila.
Often counting blak/nonblanks cells that contains a formula is a pain, maybe you can give/explain the best solutions for that.
Example: see the chapter where you explain the count blanks/non blanks, the given solution doesn’t work if there is a formula in column C [country].
love your reading glasses, make a great impact :)
a great one there...
Awesome as always 😊
Excellent!!!
Thanks for the updates; "OR"Criteria is a plus, how about nesting in "Choose"FX.?
Well done, Leila
That was very useful.
I always knew Persian ladies are amazing teachers. Sepas
Ms Gharani, as usual, a fascinating exploration of the COUNTIFS function. Think the most important thing I took away from this video is that I need reading glasses as well. Even after squinting as hard as possible, I could not read the inscription on your Award for Excellence. Congratulations.
Always a wonderful delight when you post a video. Think you are HOT with or without the reading glasses. You have been missed. God Bless you and those you care about.
🤣
Your msg pop was really creative👍
Thanks 😅
❤ GBY Beloved Soul
Where have you been?
Amazing,all are a daily need for me.thanks for your time.grateful
They don't detract from your beauty 😊
Great as always.
I like this more straightforward type of presentation without any movie style.
Maybe "Power Toys" could be a next topic for you, Leila. I was looking for ways to use the color picker in MS Word. Eventually, I installed Power Toys. The Color Picker "add-in" works system wide. Power Toys has more to offer than the color picker, alone...
Oh I love Power Toys. Use it all the time. Color picker is great. And so is fancy zones. That's the one I use daily actually. I made a video on that: ruclips.net/video/ZlyxesiiC6U/видео.htmlsi=QXqgpjZHWb-X0qts
Great and informative video as always Leila. Thanks for sharing.
You and your examples are amazing. Nice glasses 😎, gives a beautiful intellectual look.
Fantastic!!!!!
Welcome back 🎉
Countif and countifs have equal arguments order, but sumif and sumifs not. So in the last case we need to prefer use multiple criteria as common case. Also, thanks for sum tip with OR criterias. :)
Thanks a lot Leila, very useful!
Hi Leilia, I follow up your YT Chanel years algo and now I feel so confidence with use Excel functions for get AND extrac information. But now a new difficultis apper, May I want to asking you if you can change your videos for show us how present the data with grapics
Thanks very informative
Glad it was helpful!
Always brilliant
Very nice. Learned something new.