Discussion Re: Excel help in Excel
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518556#M68500
<P><LI-USER uid="521"></LI-USER> </P><P>You can shorten it a bit if you are on Office365 Insider</P><P> </P><P>=SUM(--((MMULT(--(C3:H21="YES"),SEQUENCE(COUNTA(C2:H2),1,1,0)))>0))</P><P> </P><P>Cheers</P><P>Sam</P><P> </P><P> </P>Mon, 13 Jul 2020 12:05:59 GMTexcelpbi2020-07-13T12:05:59ZExcel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518292#M68481
<P>Hi Everyone</P><P>I have some simple data on services delivered over time to individual people (ID 1 to 18) and the community group they belong to (A to F). Rows are service dates for an individual person.</P><P>I'm trying to to get a count of the number of service dates where the individual is a member of one or more of the communities - if the person is a member of more than one I only want to count one of them on the service date - so i think I want an OR function. For the data below the answer should be that there are 17 instances where a service was delivered to a person from one of the communities.</P><P> </P><P>I've tried several things including</P><P>=SUMPRODUCT((E6:E132="Yes") + (F6:F132="Yes") + (G6:G132="Yes") + (H6:H132="Yes") + (I6:I132="Yes") + (J6:J132="Yes"))</P><P> </P><P>and =COUNTIFS($E$6:$E$131,"Yes")+COUNTIFS($F$6:$F$131,"Yes")+COUNTIFS($G$6:$G$131,"Yes")+COUNTIFS($H$6:$H$131,"Yes")+COUNTIFS($I$6:$I$131,"Yes")+COUNTIFS($J$6:$J$131,"Yes")</P><P> </P><P>but always come up with '23' instead of '17'</P><P> </P><P>Please help</P><P>Thanks</P><P>Mark</P><P> </P><TABLE width="258"><TBODY><TR><TD width="64">Date</TD><TD width="38">ID</TD><TD width="28">A</TD><TD width="28">B</TD><TD width="28">C</TD><TD width="16">D</TD><TD width="28">E</TD><TD width="28">F</TD></TR><TR><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>4</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>6</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>7</TD><TD> </TD><TD> </TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>8</TD><TD> </TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>9</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD><TD> </TD></TR><TR><TD> </TD><TD>10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>11</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>12</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>16</TD><TD>Yes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>Yes</TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD> </TD><TD>6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Yes</TD></TR><TR><TD>Total</TD><TD> </TD><TD>6</TD><TD>1</TD><TD>2</TD><TD>0</TD><TD>1</TD><TD>13</TD></TR></TBODY></TABLE>Mon, 13 Jul 2020 08:58:03 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518292#M68481Mark_Bartlett2020-07-13T08:58:03ZRe: Excel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518328#M68484
<P><LI-USER uid="726709"></LI-USER> </P>
<P>For such sample</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 581px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/205000i05AE0DD89C70C5CA/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>as variant it could be</P>
<LI-CODE lang="excel">=SUMPRODUCT(--(MMULT(--($C$3:$H$21="Yes"),TRANSPOSE(COLUMN($C$3:$H$3)/COLUMN($C$3:$H$3)))>0))</LI-CODE>Mon, 13 Jul 2020 09:18:31 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518328#M68484Sergei Baklan2020-07-13T09:18:31ZRe: Excel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518474#M68497
<P><LI-USER uid="726709"></LI-USER> </P><P>Even in MS365 the number of functions that take a 2D array and aggregate to give a column or row of results as an array is minimal, hence the somewhat mathematical matrix-multiplication function MMULT. The formula</P><LI-CODE lang="excel">= SUM(SIGN(MMULT(--(data="Yes"), --TRANSPOSE(ISTEXT(community)))))</LI-CODE><P>differs from <LI-USER uid="521"></LI-USER> only in the approach taken to generating the column of 1s (I assume all data is referenced by name), and the fact that I do not require SUMPRODUCT as an array wrapper.</P><P> </P><P>I also have access to Charles Williams's SpeedTools add-in, so I am able to experiment with</P><LI-CODE lang="excel">= SUM(N(OR.ROWS(data="Yes")))</LI-CODE><P>which follows the logic of the solution somewhat better.</P><P> </P>Mon, 13 Jul 2020 11:19:01 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518474#M68497Peter Bartholomew2020-07-13T11:19:01ZRe: Excel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518556#M68500
<P><LI-USER uid="521"></LI-USER> </P><P>You can shorten it a bit if you are on Office365 Insider</P><P> </P><P>=SUM(--((MMULT(--(C3:H21="YES"),SEQUENCE(COUNTA(C2:H2),1,1,0)))>0))</P><P> </P><P>Cheers</P><P>Sam</P><P> </P><P> </P>Mon, 13 Jul 2020 12:05:59 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518556#M68500excelpbi2020-07-13T12:05:59ZRe: Excel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518573#M68503
<P><LI-USER uid="545693"></LI-USER> </P>
<P>I intentionally ignored DA functions. For this case they add practically no value but could add compatibility issues.</P>Mon, 13 Jul 2020 12:15:23 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1518573#M68503Sergei Baklan2020-07-13T12:15:23ZRe: Excel help
https://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1519770#M68559
<P><LI-USER uid="545693"></LI-USER> </P><P>Sam, there are still new techniques to be developed using DA. For example, in the present case</P><P> </P><LI-CODE lang="excel">= SUM( IFERROR((data="Yes") / COUNTIFS( data, "Yes", ID.range, ID ), 0 ) )</LI-CODE><P> </P><P>takes into account the fact that ID=6 is to be found both as record 6 and 19. In order to count the two matches as one, the above formula resorts to an old 'count unique trick' of dividing by the number of occurrences of each match.</P><P> </P><P>The problem with the formula is that COUNTIFS requires range references and NOT arrays and, moreover, the criteria ranges must be of the same type. Here 'data' is a 2D range whilst 'ID' is a column. There is no concept of 'broadcasting' for ranges so the obvious formula fails.</P><P> </P><P>What I did to make the formula work is to create a dynamic helper range 'ID.range' on another sheet using the formula</P><P> </P><LI-CODE lang="excel">= IF(ISTEXT(community), ID)</LI-CODE><P> </P><P>Since this is merely a computational device of no interest in terms of the logic of the solution, the sheet can be hidden or even 'very hidden'. In practice, the name 'ID.range' referred to </P><P> </P><LI-CODE lang="excel">=Helper!$A$1#</LI-CODE><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P>Is it worth the effort? Well, it changes the result to 16.</P><P> </P>Mon, 13 Jul 2020 20:05:55 GMThttps://techcommunity.microsoft.com/t5/excel/excel-help/m-p/1519770#M68559Peter Bartholomew2020-07-13T20:05:55Z