How to count the occurrance of character S in a range of cells but counting S as one if appearing in a contiguous range of cells

[Stella_100]

Hi and thanks in advance to whoever would like to take on this task. Please bear with me as English is not my first language, I will try to be as clear as possible. I would be very grateful if you could help me in this query.

I would like to count the occurrance of the character S as shown below but counting the character as 1 when it appears in a contiguous range of cells

A1

B1

C1

D1

E1

F1

G1

H1

I1

J1

K1

L1

M1

N1

O1

S

S

S

S

S

S

S

S

S

S

<tbody>
</tbody>

The formula should give therefore a total of 5 while using countif would give me a total of 10

I do hope I have explained everything clearly. Thank you for your help and interest. Looking forward to a reply whenever is convenient.

 

[AlanY]

the range you're counting are they just S and blank?

if so, you can just count the number of blanks + 1

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try this array formula
=SUM(IF(FREQUENCY(IF(A1:N1="S",COLUMN(A1:N1)),IF(A1:N1<>"S",COLUMN(A1:N1))),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:O2="s",COLUMN(A2:O2)),IF(1-(A2:O2="s"),COLUMN(A2:O2))),1))

This should yield a count of 5.

 

[Stella_100]

Thank you for your suggestion I can definitely make good use of that. My question constituted only part of my query and I will be posting the second question very soon, although I am finding difficult to import a table from Excel as I am not familiar with this site. Thank you again!

 

[Stella_100]

That's fantastic, it works!!! That was the first part of my query, I am going to post the second question as soon as I find out how to insert the picture of a selection of cells from Excel to show what I need to apply the formula to. Thank you very very much!

 

[Rick Rothstein]

I would think this simple formula would work where the first range covers the cells you want to examine (I assumed A1:Y1) and the second range is offset one column to the right of that range (B1:Z1)...

=COUNTIFS(A1:Y1,"S",B1:Z1,"")