Excel help

Copper Contributor

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'

 

Please help

Thanks

Mark

 

DateIDABCDEF
 1      
 2      
 3     Yes
 4Yes    Yes
 5     Yes
 6Yes     
 7  Yes   
 8 Yes   Yes
 9Yes   Yes 
 10     Yes
 11Yes    Yes
 12Yes     
 13     Yes
 14     Yes
 15     Yes
 16Yes    Yes
 1  Yes  Yes
 18     Yes
 6     Yes
Total 6120113
5 Replies

@Mark_Bartlett 

For such sample

image.png

as variant it could be

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

@Mark_Bartlett 

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.

 

I also have access to Charles Williams's SpeedTools add-in, so I am able to experiment with

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

which follows the logic of the solution somewhat better.

 

@Sergei Baklan 

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

 

 

@excelpbi 

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

@excelpbi 

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.