Convert Countif to array formula

Mmrtlm  Errata.... I wrote: ``Did you array-enter the formula by pressing ctrl+shift+Enter?``

That might not be necessary.  It is for Excel 2010 (my version).  I don't know what version you have.  Apparently, Office Excel 365 does not require that we array-enter such array formulas.  Probably true of recent standalone versions of Excel (2016 and later?).

If you have any doubts, try this experiment.  For your test formula that reference O2:O10 et al, normally-enter the COUNT formula in another row (e.g. row 11).  In Excel 2010, that results in a #VALUE error.  In Office 365 Excel, that gives the correct count.

PS.... You might also consider a formula that uses SUMPRODUCT instead of nested IF expressions.  That was more useful in older versions of Excel (like 2010) because it can be normally-entered.  But even for more recent versions of Excel, the SUMPRODUCT formula is more succinct.

Of course, you should follow your training and the instructions for the assignment, if they suggest a particular form for the solution.

Mmrtlm  wrote: ``COUNT({1,0}) returns 2, because there are two numbers inside``

Exactly right.  So that should explain why your COUNT expression does not work:  in any row where any of the conditions fails, the multiplication returns zero.  So it still "counts".

-----

Mmrtlm  wrote:  ``MAX function would return 1, but that isn't what we're looking for``

What function ("fx") works like this:  fx({1,0,1,1,0}) returns 3?

-----

Mmrtlm  wrote: ``=COUNT(IF($O$2:$O$10=AA$11,IF($M$2:$M$10=$Z$12,IF($X$2:$X$10=$Z14,1))))

I couldn't test if that works, because my last IF compairs a date with a number.``

Very good! That is best form of an array-entered formula for that logic, IMHO.

But a date __is__ a number.  So if your COUNTIFS worked in the first place, what you wrote should have the same result.

If it is not, I wonder if you have some other problem, perhaps related to the data.  For example, different types of data (not cell formats):  text vs. number.  Use ISNUMBER to determine the type of data.  Looks can be deceiving, and the cell format does not matter.

Did you array-enter the formula by pressing ctrl+shift+Enter?

Suggestion:  attach an example Excel file that demonstrates the __failure__ of the COUNT (not COUNTIFS) formula above.

-----

But I wonder why you tried the product of 3 conditions originally?  Does your assignment expect that form, specifically?

I also wondered why the instructor would ask you to change an efficient design (using COUNTIFS) with a less efficient form (array-entered COUNT).

In particular, I wonder if you should __also__ change the "and" interpretation of the 3 conditions that COUNTIFS does to an "or" interpretation.

That would indeed require some alternative formula, for example:  an array-entered COUNT form (different from the nested IF expressions); or a SUMPRODUCT formula.