SOLVED

Iron Contributor

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 column AP/AQ/AR, I want the count of "D","U" and "S" respectively for each rows

Could you please share how we can achieve this?

Br,

Anupam

6 Replies

Re: COUNT

To count the occurrences of "D," "U," and "S" in each row in columns AP, AQ, and AR, you can use the following Excel formulas. Assuming your data starts in row 2 (adjust the cell references accordingly if your data starts in a different row):

In cell AP2, you can enter the following formula to count "D"s:

=LEN(A2)-LEN(SUBSTITUTE(A2,"D",""))

In cell AQ2, you can enter the following formula to count "U"s:

=LEN(A2)-LEN(SUBSTITUTE(A2,"U",""))

In cell AR2, you can enter the following formula to count "S"s:

=LEN(A2)-LEN(SUBSTITUTE(A2,"S",""))

Drag these formulas down in columns AP, AQ, and AR to apply them to each row in your dataset.

These formulas work by first substituting the target character with an empty string in the original text and then subtracting the length of the modified text from the length of the original text. This counts the number of occurrences of the specific character in the cell.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

best response confirmed by anupambit1797 (Iron Contributor)
Solution

Re: COUNT

``=SUMPRODUCT(LEN(\$A2:\$AO2)-LEN(SUBSTITUTE(\$A2:\$AO2,AP\$1,"")))``

Does this return the intended output?

Re: COUNT

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)
)``````

Re: COUNT

As a comment, for "2DD_"  it counts D in it as 1, shall be 2.

Re: COUNT

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.

Re: COUNT

Hi@NikolinoDE , in your solution, seems the output is not correct.. as below , it should come 28 for the 1st row.. etc.

or please correct me , if I mis-interpret the formula

Thanks & Regards

Anupam Shrivastava

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

Re: COUNT

``=SUMPRODUCT(LEN(\$A2:\$AO2)-LEN(SUBSTITUTE(\$A2:\$AO2,AP\$1,"")))``

Does this return the intended output?