Discussion Re: Convert Countif to array formula in Excel
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931199#M121105
<P><LI-USER uid="521"></LI-USER> .... 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 <U><EM>class</EM></U> assignment, since it specified ``<U><EM>only</EM></U> array formulas are allowed for the final solution``. Most employers are not that specific. Anyway, it seems that <LI-USER uid="1208076"></LI-USER> is doing quite well figuring things out for himself, given good direction.</P>Fri, 05 Nov 2021 13:19:00 GMTJoe User2021-11-05T13:19:00ZConvert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930377#M121074
<P>Hey there,</P><P>I've got an assignment and only array formulas are allowed for the final solution. </P><P>It's working perfectly fine with: =COUNTIFS($O$2:$O$9995,AA$11,$M$2:$M$9995,$Z$12,$X$2:$X$9995,$Z13)</P><P>and </P><P>=COUNTIFS($P$2:$P$9995,AA$5,$M$2:$M$9995,$Z6)</P><P>But I'm having trouble converting it to an array formula. I've been trying:</P><P>=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))</P><P>for the first one, but I'm getting 9994 (aka. every row in my table) instead of 94.</P><P>Can someone help me with the conversion?</P><P> </P><P>I'm using the formula for the contents of a table, thus the <SPAN>$</SPAN></P><P> </P><P>I'd be very grateful.</P><P>Regards Tim</P>Fri, 05 Nov 2021 09:16:07 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930377#M121074Mmrtlm2021-11-05T09:16:07ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930669#M121081
<P><LI-USER uid="1208076"></LI-USER> .... 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.</P><P> </P><P>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?</P><P> </P><P>PS.... You might use Formula Evaluation to see the operation of your COUNT formula. Of course, try it with a much range of rows.</P><P> </P><P>-----</P><P>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?</P><P> </P><P>I would use nested IF expressions to make that happen in an array-entered formula.</P><P> </P><P>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.</P><P> </P><P>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.</P>Fri, 05 Nov 2021 10:31:59 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930669#M121081Joe User2021-11-05T10:31:59ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930707#M121083
<P><LI-USER uid="1208076"></LI-USER> </P>
<P>Don't add zero, FALSE will be ignored by COUNT().</P>
<LI-CODE lang="applescript">=COUNT( IF($O$2:$O$9995=AA$11,1) *
IF($M$2:$M$9995=$Z$12,1) *
IF($X$2:$X$9995=$Z13, 1) )</LI-CODE>
<P>shall work</P>Fri, 05 Nov 2021 10:38:07 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930707#M121083Sergei Baklan2021-11-05T10:38:07ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930913#M121096
<P><LI-USER uid="146717"></LI-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.</P><P>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.</P><P> </P><P>The COUNTIF only counts a row if all three IFs are true.</P><P>Applying that I got: </P><P>=COUNT(IF($O$2:$O$10=AA$11,IF($M$2:$M$10=$Z$12,IF($X$2:$X$10=$Z14,1))))</P><P>I couldn't test if that works, because my last IF compairs a date with a number. </P><P> </P><P>Is there another function that is better suited for the assignment or should it work just fine with the nested IFs?</P><P> </P><P> </P><P> </P>Fri, 05 Nov 2021 11:55:16 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2930913#M121096Mmrtlm2021-11-05T11:55:16ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931072#M121100
<P><LI-USER uid="521"></LI-USER> wrote: ``=COUNT( IF($O$2:$O$9995=AA$11,1) * IF($M$2:$M$9995=$Z$12,1) *<BR />IF($X$2:$X$9995=$Z13, 1) ) shall work``.</P><P> </P><P>What does COUNT(FALSE <STRONG><FONT color="#FF0000">*</FONT></STRONG> FALSE) return, and why?</P>Fri, 05 Nov 2021 12:41:46 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931072#M121100Joe User2021-11-05T12:41:46ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931102#M121101
<P><LI-USER uid="146717"></LI-USER> </P>
<P>You are right, I was too fast. It could be</P>
<LI-CODE lang="excel-formula">=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 )</LI-CODE>Fri, 05 Nov 2021 12:49:08 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931102#M121101Sergei Baklan2021-11-05T12:49:08ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931186#M121104
<P><LI-USER uid="1208076"></LI-USER> wrote: ``COUNT({1,0}) returns 2, because there are two numbers inside``</P><P> </P><P>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".</P><P> </P><P>-----</P><P><LI-USER uid="1208076"></LI-USER> wrote: ``MAX function would return 1, but that isn't what we're looking for``</P><P> </P><P>What function ("fx") works like this: fx({1,0,1,1,0}) returns 3?</P><P> </P><P>-----</P><P><LI-USER uid="1208076"></LI-USER> wrote: ``=COUNT(IF($O$2:$O$10=AA$11,IF($M$2:$M$10=$Z$12,IF($X$2:$X$10=$Z14,1))))</P><P>I couldn't test if that works, because my last IF compairs a date with a number.``</P><P> </P><P>Very good! That is best form of an array-entered formula for that logic, IMHO.</P><P> </P><P>But a date __is__ a number. So if your COUNTIFS worked in the first place, what you wrote should have the same result.</P><P> </P><P>If it is not, I wonder if you have some other problem, perhaps related to the data. For example, different <U><EM>types</EM></U> of data (not cell formats): text vs. number. Use ISNUMBER to determine the <U><EM>type</EM></U> of data. Looks can be deceiving, and the cell format does not matter.</P><P> </P><P>Did you array-enter the formula by pressing ctrl+shift+Enter?</P><P> </P><P>Suggestion: attach an example Excel file that demonstrates the __failure__ of the COUNT (not COUNTIFS) formula above.</P><P> </P><P>-----</P><P>But I wonder why you tried the product of 3 conditions originally? Does your assignment expect that form, specifically?</P><P> </P><P>I also wondered why the instructor would ask you to change an efficient design (using COUNTIFS) with a less efficient form (array-entered COUNT).</P><P> </P><P>In particular, I wonder if you should __also__ change the "and" interpretation of the 3 conditions that COUNTIFS does to an "or" interpretation.</P><P> </P><P>That would indeed require some alternative formula, for example: an array-entered COUNT form (different from the nested IF expressions); or a SUMPRODUCT formula.</P>Fri, 05 Nov 2021 13:17:49 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931186#M121104Joe User2021-11-05T13:17:49ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931199#M121105
<P><LI-USER uid="521"></LI-USER> .... 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 <U><EM>class</EM></U> assignment, since it specified ``<U><EM>only</EM></U> array formulas are allowed for the final solution``. Most employers are not that specific. Anyway, it seems that <LI-USER uid="1208076"></LI-USER> is doing quite well figuring things out for himself, given good direction.</P>Fri, 05 Nov 2021 13:19:00 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2931199#M121105Joe User2021-11-05T13:19:00ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2932418#M121158
<P><LI-USER uid="1208076"></LI-USER> Errata.... I wrote: ``Did you array-enter the formula by pressing ctrl+shift+Enter?``</P><P> </P><P>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?).</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Of course, you should follow your training and the instructions for the assignment, if they suggest a particular form for the solution.</P>Fri, 05 Nov 2021 18:29:20 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2932418#M121158Joe User2021-11-05T18:29:20ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2932599#M121169
<P><LI-USER uid="1208076"></LI-USER> </P><P>=COUNT(($O$2:$O$9995=AA$11)*($M$2:$M$9995=$Z$12) * ($X$2:$X$9995=$Z13))</P><P>or</P><P>=SUMPRODUCT(($O$2:$O$9995=AA$11)*($M$2:$M$9995=$Z$12) * ($X$2:$X$9995=$Z13))</P>Fri, 05 Nov 2021 19:12:07 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2932599#M121169Juliano-Petrukio2021-11-05T19:12:07ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2935641#M121300
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.<BR /><BR />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.<BR /><BR />It does work for me without the {}, but I'll keep them in hopes that it makes my assigner happy.Sun, 07 Nov 2021 10:22:12 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2935641#M121300Mmrtlm2021-11-07T10:22:12ZRe: Convert Countif to array formula
https://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2936210#M121320
<P><LI-USER uid="1208076"></LI-USER> 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.``</P><P> </P><P>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.</P><P> </P><P>It's a moot point, since you indicate that you completed the assignment to your satisfaction.</P><P> </P><P>But it might be helpful in the future.</P>Sun, 07 Nov 2021 15:09:40 GMThttps://techcommunity.microsoft.com/t5/excel/convert-countif-to-array-formula/m-p/2936210#M121320Joe User2021-11-07T15:09:40Z