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.
JoeUser2004 Well count returns the amount of numbers inside, so the COUNT({1,0}) returns 2, because there are two numbers inside. The MAX function would return 1, but that isn't what we're looking for.
I saw that their is a COUNT2 function, but as far as I can tell it does the same. The problem with the count is that it is doing a matrix multiplication and therefore keeping a 0 for each row I enter and then counting those, thus it always returns the exact amount of rows I enter. It could work with a function that only counts the 1. As I am very new to excel I bearly know any functions. Maybe you can hint me in the right direction.
The COUNTIF only counts a row if all three IFs are true.
Applying that I got:
=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.
Is there another function that is better suited for the assignment or should it work just fine with the nested IFs?
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.