Forum Discussion
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...
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 SergeiBaklan 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.