Jul 13 2020 01:58 AM
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
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 |
Jul 13 2020 02:18 AM
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))
Jul 13 2020 04:19 AM
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.
Jul 13 2020 05:05 AM
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
Jul 13 2020 05:15 AM
I intentionally ignored DA functions. For this case they add practically no value but could add compatibility issues.
Jul 13 2020 01:05 PM
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.