+1 for every letter "S" [Closed]

Report
Posts
1
Registration date
Saturday July 8, 2017
Status
Member
Last seen
July 8, 2017
-
Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
-
I am trying to add 1 for every letter "s" between C5:C53,F5:F53,I5:I53.

So far I have this formula

=SUM(IF(C5:C53="S",1,0))

Which is giving me a #VALUE! error. Not what the issue is.

3 replies

Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
141
bored2, Good afternoon.

Your formula is a ARRAY formula.

Array formulas are often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter, you press Ctrl+Shift+Enter to complete the formula.

When you press Ctrl+Shift+Enter, Excel surrounds the formula with brace characters ({ }.

Try to do this and tell us if it worked for you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Thank you for the help. CSE (Ctrl+Shift+Enter) did fix the #value error but now it's giving me the error "You've entered too many arguments for this function". The full formula I have so far is =SUM(IF(C5:C53,F5:F53,I5:I53="S",1,0))
Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
141
Bored2, Good morning.

You can use a normal formula version with COUNTIF.
=SUM(COUNTIF(C5:C53,"S"),COUNTIF(F5:F53,"S"),COUNTIF(I5:I53,"S"))

If you are sure that in columns between columns C and I there is no possibility of any cells with S content, then try using: =COUNTIF(C5:I53,"S")

Please, tell us if it worked as you desired.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Thank you very much for your help. I was able to get the =COUNTIF(C5:I53,"S") formala to work and it works perfectly and does the job. The =SUM(COUNTIF(C5:C53,"S"),COUNTIF(F5:F53,"S"),COUNTIF(I5:I53,"S")) formula does not seem to work for me. Not sure why. I'm not getting an error and I am pressing Ctrl+Shift+Enter to complete.
Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
141
Bored2, Good morning.

As I said before:
"...You can use a normal formula version with COUNTIF. ..."
then you can not use Ctrl+Shift+Enter to complete.

Just press ENTER to complete this formula.

Try it and tell us if it is working.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!