I am trying to count how many x are in a row but if there are blanks between sets of xs then it would start a second count - GoogleSheets

Question

On Google Sheets - I am trying to count how many x are in a row but if there are blanks between sets of xs then it would start a second count

The headers are dates, except for a description header

If my row has x on monday x on tuesday x on wednesday blanks for the next 3 days and then an x on sunday - I want the count for xs to be broken into two sets 3 and 1 instead of counting 4 for the whole row. This can be over a multi-month period.

Or if it can't count that way then if it is possible to split rows when there is a space between x's and create a new row with the remaining x's and both rows include the description columns with

I have tried countifs

In the first row I want the count to be 9, in the second row I want the count to be 3 and 5 - I want to restart the count everytime there is a blank.

https://docs.google.com/spreadsheets/d/1feu2YGNaM5SBBpdZLLWrKgVtg14ECfYfmyXtG1oslFE/edit?usp=sharing

I would like it to either count out to the right like I have on the first tab, or create a table like the second tab and give me the counts per type. If I need to change the x's and blanks to another thing I can like 0s and 1s

Answer 1

Here's a possible solution:

=ARRAYFORMULA(
   BYROW(B2:AF,
     LAMBDA(row,
       IF(COUNTA(row)=0,,
          LEN(SPLIT(JOIN(,IF(row="",0,"|")),0))))))

For each row, the formula is converting all the empty values in the row with 0 and all the non-empty values with |, then it's joining them using the JOIN function and splitting them by the 0 using the SPLIT function. Finally, it takes the length of the resulting array using the LEN function.

Note: This formula will only work if your range doesn't span across more than 50000 columns.