Count every 15th non-empty cell in row

[Whitestar127]

Hi, consider this sheet (row 1 is shown in the image):

I'm looking for a function to count the number of names in row 1 from BE1 all the way to UU1. Only as far as DL1 is shown in the image above because of lack of space.
It should count every 15th cell as that is the distance (in number of columns) between each name.

So basically count every 15th non-empty cell starting from BE1. There are currently only 3 names: Alex, Alfred, and Anders. So the function should return 3, since cells CX1 to UU1 currently contain no names.

I'm guessing the function should contain COUNTIF, MOD and COLUMN or something similar, but I can't get my head around how to do it exactly.

Can anyone help?

 

[FormR]

Hi, here's one option you could try:

=COUNTA(LET(c,INDEX(BE1:UU1,SEQUENCE(35,,1,15)),FILTER(c,c<>"")))

 

[Whitestar127]

Color me impressed! That worked perfectly!! Thanks a lot!

Just curious, this will work on all 365 versions I assume. But will it work on older versions of Excel?

Also, if you have time could you explain that Sequence function? For instance, where does 35 come from?
EDIT: Oh never mind. Number of rows to return.

 

[FormR]

But will it work on older versions of Excel?

I think the SEQUENCE() and FILTER() functions have been around since XL2021

 

[Whitestar127]

Btw, there is one small peculiarity: It returns 1 even if there are 0 names present.

0 names --> 1
1 name --> 1

I tried to change the Start parameter to 0 but that didn't work.

 

[FormR]

Ah, ok - here's an alternative you could try:

=COUNT(1/(INDEX(BE1:UU1,SEQUENCE(35,,1,15))<>""))

 

[Whitestar127]

Perfect! Thanks