Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 18, 2025
Solved

Count U slots

Dear Experts, 

                    I have a case like below:-

So, there's a Worksheet "Calc" below where we have Slots from 0~19 two times from Col (C~V) and (X~AQ);

In ,Sheet2 we have the Logic on how to count the U slots:-

1D --> 1 symbol DL
2DD --> 2 symbols DL
2DU/2UD --> 1 symbol for UL, 1 symbol for DL
1U --> 1 symbol UL
2UU --> 2 symbols UL
1S (for split) --> Half symbol for UL, Half symbol for DL

Can you please provide a formula for Column AS and AT to count the U slots, using above Logic,

 

Attached is the Worksheet.

  • In AS2:

    =SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2, "U", "")))+SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2, "S", "")))/2

    In AT2:

    =SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2, "U", "")))+SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2, "S", "")))/2

    Fill down.

2 Replies

  • In AS2:

    =SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2, "U", "")))+SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2, "S", "")))/2

    In AT2:

    =SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2, "U", "")))+SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2, "S", "")))/2

    Fill down.

Resources