Forum Discussion
Convert Countif to array formula
Hey there,
I've got an assignment and only array formulas are allowed for the final solution.
It's working perfectly fine with: =COUNTIFS($O$2:$O$9995,AA$11,$M$2:$M$9995,$Z$12,$X$2:$X$9995,$Z13)
and
=COUNTIFS($P$2:$P$9995,AA$5,$M$2:$M$9995,$Z6)
But I'm having trouble converting it to an array formula. I've been trying:
=COUNT(IF($O$2:$O$9995=AA$11,1,0)*IF($M$2:$M$9995=$Z$12,1,0)*IF($X$2:$X$9995=$Z13,1,0))
for the first one, but I'm getting 9994 (aka. every row in my table) instead of 94.
Can someone help me with the conversion?
I'm using the formula for the contents of a table, thus the $
I'd be very grateful.
Regards Tim
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.
11 Replies
=COUNT(($O$2:$O$9995=AA$11)*($M$2:$M$9995=$Z$12) * ($X$2:$X$9995=$Z13))
or
=SUMPRODUCT(($O$2:$O$9995=AA$11)*($M$2:$M$9995=$Z$12) * ($X$2:$X$9995=$Z13))
Don't add zero, FALSE will be ignored by COUNT().
=COUNT( IF($O$2:$O$9995=AA$11,1) * IF($M$2:$M$9995=$Z$12,1) * IF($X$2:$X$9995=$Z13, 1) )shall work
SergeiBaklan wrote: ``=COUNT( IF($O$2:$O$9995=AA$11,1) * IF($M$2:$M$9995=$Z$12,1) *
IF($X$2:$X$9995=$Z13, 1) ) shall work``.What does COUNT(FALSE * FALSE) return, and why?
You are right, I was too fast. It could be
=COUNT( IF( IF($O$2:$O$9995=AA$11,1) * IF($M$2:$M$9995=$Z$12,1) * IF($X$2:$X$9995=$Z13, 1), 1 )
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.
