Forum Discussion
Solved
COUNT
Dear Experts, Greetings! In attached file names "COUNT" We have 3 variables "D", "U" and "S" 2DU => means 1 D and 1 U 2SU => means 1 S and 1 U, In the...
=SUMPRODUCT(LEN($A2:$AO2)-LEN(SUBSTITUTE($A2:$AO2,AP$1,"")))Does this return the intended output?
This is a 365 solution:
=LET(
height, ROWS(grid),
width, 3,
DUS, {"*D*", "*U*", "*S*"},
CountDUS, LAMBDA(r, c,
LET(
ThisRow, TAKE(DROP(grid, r - 1), 1),
Criteria, INDEX(DUS, c),
DD, COUNTIF(ThisRow, "2DD_"),
Total, COUNTIF(ThisRow, Criteria),
IF(c = 1, DD + Total, Total)
)
),
MAKEARRAY(height, width, CountDUS)
)
As a comment, for "2DD_" it counts D in it as 1, shall be 2.
Thank you. I've updated my post.
Another approach would be to table the data and use 3 COUNTIFs. Possible drawbacks: increased workbook size with the table and possibly slower calculation time? I don't have a vba timer at the moment.