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