Forum Discussion
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))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
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.
I intentionally ignored DA functions. For this case they add practically no value but could add compatibility issues.
