 # Excel help

Hi Everyone

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.

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.

I've tried several things including

=SUMPRODUCT((E6:E132="Yes") + (F6:F132="Yes") + (G6:G132="Yes") + (H6:H132="Yes") + (I6:I132="Yes") + (J6:J132="Yes"))

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

but always come up with '23' instead of '17'

Thanks

Mark

 Date ID A B C D E F 1 2 3 Yes 4 Yes Yes 5 Yes 6 Yes 7 Yes 8 Yes Yes 9 Yes Yes 10 Yes 11 Yes Yes 12 Yes 13 Yes 14 Yes 15 Yes 16 Yes Yes 1 Yes Yes 18 Yes 6 Yes Total 6 1 2 0 1 13
5 Replies

# Re: Excel help

For such sample as variant it could be

``=SUMPRODUCT(--(MMULT(--(\$C\$3:\$H\$21="Yes"),TRANSPOSE(COLUMN(\$C\$3:\$H\$3)/COLUMN(\$C\$3:\$H\$3)))>0))``

# Re: Excel help

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

``= SUM(SIGN(MMULT(--(data="Yes"), --TRANSPOSE(ISTEXT(community)))))``

differs from @Sergei Baklan  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.

``= SUM(N(OR.ROWS(data="Yes")))``

which follows the logic of the solution somewhat better.

# Re: Excel help

You can shorten it a bit if you are on Office365 Insider

=SUM(--((MMULT(--(C3:H21="YES"),SEQUENCE(COUNTA(C2:H2),1,1,0)))>0))

Cheers

Sam

# Re: Excel help

I intentionally ignored DA functions. For this case they add practically no value but could add compatibility issues.

# Re: Excel help

Sam, there are still new techniques to be developed using DA.  For example, in the present case

``= SUM( IFERROR((data="Yes") / COUNTIFS( data, "Yes", ID.range, ID ), 0 ) )``

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.

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.

What I did to make the formula work is to create a dynamic helper range 'ID.range' on another sheet using the formula

``= IF(ISTEXT(community), ID)``

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

``=Helper!\$A\$1#``

Is it worth the effort?  Well, it changes the result to 16.