Hi Mike, I read Greg Doodle's post and your comment: then I came to the following. Since all the patients required are highligted, you can filter by color!! And if you transform this list to an Excel Table you can add a total row (count patients). this works fine! I also added 3 conditional formatting per column in in order to check the final result. btw: it is Cell $A18 and not $A5 in the conditional formatting.
Fascinating video! Thank you very much! Since in my line of work, it's especially important that everybody can easily understand and operate formulas, I would've created a helper column with (IF(A18=$G$24,1,0)&IF(B18>=$H$24,1,0)&IF(C18>=$I$24,1,0))*1 and then entered 1 in H43. Then I can do COUNTIF(E18:E41,">="&H43). It isn't nearly as elegant as SUMPRODUCT but it would easily let me change the count to FI people who meet the first and third criteria by changing H43 to 101.
Thanks Mike..!! I gotta have Excel 2016 version to use formula text. I had a look at another video of yours EMT 462 which addressed my concern, regardless of Excel version. Another option was UDF.
Mike thanks for showing the different methods for counting. Hey I bought your book Control+Shift+Enter an few years ago when I first started studying Excel. Being a beginner, I was learning something new on every page flip. I'm about to revisit the book so I have some of that wizardry engrained in my head.
Yes, it is always good to double back and re-read books. The second and third times through will yield many new insights! Thanks for your support with buying the books and with your comment, Thumbs Up and Sub : )
I just saw a video of Bill Jelen to sort by colour. now if you add a conditional formatting per column this works on your dataset as well, but it's only a visual presentation, you can not get a number out of it. Unless you use VBA....
On page 121 in my Ctrl + Shift + Enter book I timed similar formulas and SUMPRODUCT was significantly longer in calculating speed than the Database function. I did not time these specific formulas. Did you?
Thank you Mike, nice to see all the options. All? I have a question, if you convert the list to a table, would it be possible to use Slicers. Normaly they would act as AND, but is is there a way you can use slicers as OR ?? (of course you can only select a value/text, not > or
How do you adapt one of those formulas to highlight the record if one of those conditions is met? I know how to do it by using 3 separate conditional formats, but it would be a neat trick to do it with just one entry. I hope I was clear...probably not lol
![Chicken P - People's Favorite (Remix) [Feat. 42 Dugg] (Official Video)](http://i.ytimg.com/vi/a6Bn8FEtxmY/mqdefault.jpg)
I never seem to get enough of these videos. I see them and see them again!!!!
That is so awesome and is a direction function of how much fun Excel is : ) Thanks for your amazing support, John!!!
Hi Mike, I read Greg Doodle's post and your comment: then I came to the following. Since all the patients required are highligted, you can filter by color!! And if you transform this list to an Excel Table you can add a total row (count patients). this works fine! I also added 3 conditional formatting per column in in order to check the final result.
btw: it is Cell $A18 and not $A5 in the conditional formatting.
Awesome!!!
Love it! Thanks for the different methods - my favorite is the SUMPRODUCT one :)
Thanks, Teammate!
Excel-lent examples! Thanks for giving not 1, not 2 but 3 examples!
Yes, three examples is triple the fun : )
It's just to much for me to handle your Tricks Mr. Mike absolutely you are the king of Excel
Glad you like them, as always Ismail!!!!
I prefer your SUMPRODUCT solution but the DCOUNTA is also a great way to do it. Thanks for the video Mike!
You are welcome!
Fascinating video! Thank you very much! Since in my line of work, it's especially important that everybody can easily understand and operate formulas, I would've created a helper column with (IF(A18=$G$24,1,0)&IF(B18>=$H$24,1,0)&IF(C18>=$I$24,1,0))*1 and then entered 1 in H43. Then I can do COUNTIF(E18:E41,">="&H43). It isn't nearly as elegant as SUMPRODUCT but it would easily let me change the count to FI people who meet the first and third criteria by changing H43 to 101.
Cool!
Thanks Mike..!!
I gotta have Excel 2016 version to use formula text. I had a look at another video of yours EMT 462 which addressed my concern, regardless of Excel version. Another option was UDF.
This is very helpful. Thank you sir.
You are welcome!
Thanks Mike. Sumproduct is the best, but the Dcounta was very interesting to. Amazing Mike. I hope that we will have more of these :)
More and more, as long as viewers keep support : ) Thank you as always for your support, John : )
Mike, you're the best. Thanks so much.
You are welcome!
Nice comparison of different methods. Even thought Dcounta is easiest solution for this case, I prefer Sumproduct
There ARE so many EXCELlent ways to have fun in Excel!
Amazing sumproduct, Tons of thanks Mike.
Glad you like it! Thanks for your support : )
Mike thanks for showing the different methods for counting. Hey I bought your book Control+Shift+Enter an few years ago when I first started studying Excel. Being a beginner, I was learning something new on every page flip. I'm about to revisit the book so I have some of that wizardry engrained in my head.
Yes, it is always good to double back and re-read books. The second and third times through will yield many new insights! Thanks for your support with buying the books and with your comment, Thumbs Up and Sub : )
Nick you are right, this book is not a one time reading device... you can skip the parts to compare the calculation speed.
I guess all good books can be read many times. In fact, I still use the book myself when I forget something : )
I just saw a video of Bill Jelen to sort by colour. now if you add a conditional formatting per column this works on your dataset as well, but it's only a visual presentation, you can not get a number out of it. Unless you use VBA....
Hi, thank you sir Yoda excel i love your courses
Love is good! I am glad that the videos help - thanks for your support : )
Always learn something new, thanks Mike!
You are welcome!
Good stuff! I've gotta dig into those database functions.
Thank, Oz!!!! I can Dig IT, those Database Functions, that is : )
Amazing..Thank you so much
Wonderful!...I like it, thank you Mike.
You are welcome, nimer!!! Thanks for your support : )
Wow! I love double negative! I've learned that from you :)
Awesome! I am happy that you can learn some cool, fun AND Efficient tricks from my videos : )
Always something :). Since I've learned -- I use it all the time :)
That's cool as always....I think sumproduct is faster than sum array and dcounta
You are welcome! What do you mean by faster? Do you means to type the formula, or calculation speed?
calculation speed. 😊
On page 121 in my Ctrl + Shift + Enter book I timed similar formulas and SUMPRODUCT was significantly longer in calculating speed than the Database function. I did not time these specific formulas. Did you?
Ok... I haven't tried yet. I'll try this weekend and let you know. I thought DFunction would take time to calculate.
Great mike as always ...Great
Glad you like it : )
it is super,
Thumbs up....
Glad it was super for you, Sevag!!! Thanks for the support - it really helps : )
Thanks for this EXCELlent video amazing Mike :)
You are welcome, Syed!!!! Thanks for your EXCElent support : )
Thumbs up!
Thanks for the Thumbs Up, Teammate : )
Thanks Mike for the post. What an array formula..!!
How you get to display a formula as a text in another cell?
Here is a video i made about thos topic: ruclips.net/video/k0vLC0rGySc/видео.html
Thanks for the support with your comment, Thumbs Up and Sub : )
Thank you a lot )))
You are welcome a lot!!!! Thanks for the support with your comment, Thumbs Up an Sub : )
Already done))
Yes!!!!! : )
nice and helpful
Glad it is helpful, aamir!!! Thanks for your support : )
Thank you Mike, nice to see all the options. All? I have a question, if you convert the list to a table, would it be possible to use Slicers. Normaly they would act as AND, but is is there a way you can use slicers as OR ?? (of course you can only select a value/text, not > or
I am not sure how to have OR Logical Test on different Columns with a Slicer...
Can we get the link for same data for the practice...
Of course! It is below video, first link : )
Thanks for your support with Thumbs Up and Sub!
=COUNTA(D18:D41)-COUNTIFS(A18:A41,""&F24,B18:B41,"
How do you adapt one of those formulas to highlight the record if one of those conditions is met?
I know how to do it by using 3 separate conditional formats, but it would be a neat trick to do it with just one entry.
I hope I was clear...probably not lol
If you download the workbook, highlight the records in the table, open Conditional Formatting Manage Rules, you will see the formula that I used : )
The formula is: =OR($A5=$F$24,$B5>=$G$24,$C5>=$H$24)
ExcelIsFun every time I think I'm an advanced Excel user, you remind me that I have a LOT to learn.....thanks for all you do
You are welcome! I am happy to help our Awesome & EXCELlent Online Excel team!
Hey Mike, I salute your logical videos, I have learn more tricks from your videos, Ton of Thanks Mike.
We eliminate one criteria by sending the smokers to a chimney specialist. ...? !
Yes!
Don't do drugs :( Do Excel with ExcelIsFun :)
Yes!!!