Convert Countif to array formula

[Mmrtlm]

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

[#2 · Accepted solution]

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

[#3]

@Mmrtlm 

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

[#4]

@Joe User 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?

[#5]

@Sergei Baklan  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?

[#6]

@Joe User 

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 )

[#7]

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

[#8]

@Sergei Baklan  .... I try not to spoon-feed solutions when we know that it is an "assignment".  Granted, it might be a work assignment.  But I think it is a class assignment, since it specified ``only array formulas are allowed for the final solution``.  Most employers are not that specific.  Anyway, it seems that @Mmrtlm  is doing quite well figuring things out for himself, given good direction.

[#9]

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

[#10]

@Mmrtlm 

=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))

[#11]

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.

[#12]

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