Forum Discussion
Convert Countif to array formula
Mmrtlm .... Your array-entered (?) COUNT formula is not the best way to do it, but it is a good first effort that can be made to work.
To understand why your does not, answer for yourself: why does COUNT({1,0}) return 2? Given that it does, what alternative function would return 1 instead?
PS.... You might use Formula Evaluation to see the operation of your COUNT formula. Of course, try it with a much range of rows.
-----
For a better solution, explain for youself as if to someone else: what condition does COUNTIF($O$2:$O$9995,AA$11,$M$2:$M$9995,$Z$12,$X$2:$X$9995,$Z13) count as 1?
I would use nested IF expressions to make that happen in an array-entered formula.
But you seem to know how to use multiplication to effect an AND operation, since we cannot use AND itself in an array-entered formula and have it work as a row-by-row opertion.
Sorry, but since the assignment is to craft an array-entered formula, I cannot offer turnkey solutions or explanations. After all, that is __your__ assignment to do.
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.
I fixed the problem with the date type and now the formula is working perfectly. Thank you very much for your guidence. My assignment doesn't expect the product of 3 conditions, only that I use an array formula, even though it's less efficient.
It does work for me without the {}, but I'll keep them in hopes that it makes my assigner happy.
Mmrtlm wrote: ``An fx({1,0,1,1,0}) that returns 3, would have to be a function that counts the true values and not the false values, I don't know what it would be called though.``
No. It merely needs to "sum" all the values (wink). Note that adding zero does not change the sum. And adding one for each row that meets all 3 conditions is the same as counting them.
It's a moot point, since you indicate that you completed the assignment to your satisfaction.
But it might be helpful in the future.