Excel Magic Trick 1482: SUMPRODUCT, DCOUNTA or SUM & IF for Counting with OR Logical Test

In this video learn how to Count with an OR Logical Test based on Three Columns. See a Pharmacy Data Set with Patient Data with the goal of counting how many patients are eligible for the service based on an OR Logical Test. The difficultly in this calculation is that we need to avoid Double Counting. See three formulas: 1) SUM and IF Function Array Formula, which requires Ctrl + Shift + Enter, 2) SUMPRODUCT Array Formula, which does not Require Ctrl + Shift + Enter, and 3) DCOUNTA database function, which is not an Array Formula but has an unusual Criteria Requirement.
Learn all about Array Formulas, what they are and how to create them.
Topics:
1. (00:01) Introduction & Discussion of pitfalls of OR Logical Tests when there is the chance of Double Counting
2. (03:05) Formula #1: SUM and IF Function Array Formula, which requires Ctrl + Shift + Enter
3. Formula #2: SUMPRODUCT Array Formula, which does not Require Ctrl + Shift + Enter
4. Formula #3: DCOUNTA database function, which is not an Array Formula but has an unusual Criteria Requirement.
5. Summary

[johnborg]

I never seem to get enough of these videos. I see them and see them again!!!!

[barttitulaerexcelbart]

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.

[DougHExcel]

Excel-lent examples! Thanks for giving not 1, not 2 but 3 examples!

[LeilaGharani]

Love it! Thanks for the different methods - my favorite is the SUMPRODUCT one :)

[MySpreadsheetLab]

I prefer your SUMPRODUCT solution but the DCOUNTA is also a great way to do it. Thanks for the video Mike!

[ennykraft]

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.

[ismailismaili]

It's just to much for me to handle your Tricks Mr. Mike absolutely you are the king of Excel

[johnborg]

Thanks Mike. Sumproduct is the best, but the Dcounta was very interesting to. Amazing Mike. I hope that we will have more of these :)

[TreyStegall]

Mike, you're the best. Thanks so much.

[chrism]

Always learn something new, thanks Mike!

[AbhisheakSaraswat]

Amazing sumproduct, Tons of thanks Mike.

[OzduSoleilDATA]

Good stuff! I've gotta dig into those database functions.

[ahmedal-dossary]

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.

[nimerassad]

Wonderful!...I like it, thank you Mike.

[vida]

Nice comparison of different methods. Even thought Dcounta is easiest solution for this case, I prefer Sumproduct

[Hackzinou]

Hi, thank you sir Yoda excel i love your courses

[MalinaC]

Wow! I love double negative! I've learned that from you :)

[nsanch]

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.

[SyedMuzammilMahasanShahi]

Thanks for this EXCELlent video amazing Mike :)

[barttitulaerexcelbart]

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....