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

New Member
Joined
Nov 11, 2018
Messages
3
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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]C1
[/TD]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[TD]I1
[/TD]
[TD]J1
[/TD]
[TD]K1
[/TD]
[TD]L1
[/TD]
[TD]M1
[/TD]
[TD]N1
[/TD]
[TD]O1
[/TD]
[/TR]
[TR]
[TD]S
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD]S
[/TD]
[TD]S
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD]S
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
the range you're counting are they just S and blank?

if so, you can just count the number of blanks + 1
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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! :)
 
Upvote 0
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!
 
Upvote 0
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,"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top