Excel Magic Trick 980: OR and AND Counting Criteria In One Formula, Boolean or MATCH?

Formula Goal: Count how many Account Executives or Recruiters are in one column if the date column contains a date.
Topics:
Compare and contrast Boolean Logic or the MATCH function for building OR Criteria into an OR and AND criteria counting formula.
1. Formula 1: SUMPRODUCT, Boolean and ISNUMBER function
2. Formula 2: SUMPRODUCT, Boolean and NOT criteria
3. Formula 3: SUMPRODUCT, MATCH and ISNUMBER functions
4. We will time each formula
5. Boolean: Advantage is that it calculates faster, Disadvantage is that it may be tedious to create.
6. MATCH: Advantage is that it it is easy to create, Disadvantage is that it may slow down formula calculation time.

[excelisfun]

I am glad that you like it!

Happy Holidays to you and our online Excel Team!!!

[excelisfun]

That is just great! And it is much faster calculating! hamy72 to the rescue!!!

Simply superb!!

[excelisfun]

Yes, it will be at Amazon. In fact, it is already listed at Amazon with a release date of about June 2013. You can go and like the book and do whatever else there is to do at Amazon to pump the book up!!! Thanks for the support!

[excelisfun]

Not for a while, not until about June 2013... But when writing a book, it has to be done very far in advance because there are many editors and processes before getting to the final product.

[excelisfun]

I am sure that it will. The Publisher, Bill "Mr Excel" Jelen at Holy Macro Books usually has it available in many places. Mr Excel web site, Amazon and others.

[excelisfun]

Yes, a book called "Slaying Excel Dragons". A DVD with the same name also.

[excelisfun]

For array calculations, VLOOKUP is not as accommodating as other lookup functions, like LOOKUP, INDEX, MATCH. (VLOOKUP can do some array calculations, but just not as accommodating as the others).

To time formulas you need VBA code. Charles Williams has great code here:
msdn.microsoft  [dot]

[excelisfun]

I am not sure how pre-sales will work yet. I'll let you know if there is some pre-sale deal. Thanks for the support by considering to buy the DVD / Book!

[excelisfun]

and ask the publisher. I am pretty sure that it is available because I know at least a few people in India that have the book. The current book I have out is: "Slaying Excel Dragons". The next book, due out in spring 2013 is "Ctrl + Shift + Enter: Mastering Array Formulas".

[JuanEsparzaReyes]

This is awesome, Happy Holidays to every body.

[excelisfun]

SUMPRODUCT does not understand TRUEs and FALSEs. Any math operation on TRUES and FALSEs converts them to 1s and 0s. The mots efficicnet way is Double Negative. I have a few videos about this topic. Here is one:

Excel Magic Trick 314: Convert TRUE & FALSE to 1 and 0

Here is another good one:

Excel Magic Trick 777: SUMPRODUCT Function -- Basics To Advanced (14 Examples)

The Array Formula Book that I have coming out in Spring will talk about that a lot also (and do tests to try and prove it).

[excelisfun]

Actually it is Book and DVD ( I am actually doing the DVD first and then writing the book...).

[krn]

Thanks Mike. Looking forward to the book. Are you going to have it available on Amazon maybe?

[chungkwanming]

Thanks for the trick.

It's interesting because I've tried using match() as an array function as you did but vlookup() cannot be used as an array function though they are lookup functions.

And where did you get the RangeTimer to calculate formula time?

[emaileverywhere]

Great those formulas. Learning a lot of it! Will the book be available in Europe as well?

Thanx/Excelbat

[AnandPenmatcha]

Dear Mike Grivin,

Congratulations, when this DVD & book will be available in Stores

[aryesegal]

You've published a book?! I'm gonna get it! Available on amazon? where can i buy it from? Thanks! :]

[irodriguez]

I guess the book has to be out by now, where can we get it?