+1 for every letter "S"

Closed
bored2 Posts 1 Registration date Saturday July 8, 2017 Status Member Last seen July 8, 2017 - Jul 8, 2017 at 11:00 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Jul 12, 2017 at 11:04 AM
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.
Related:

3 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 9, 2017 at 03:41 PM
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
0
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))
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 11, 2017 at 08:32 AM
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
0
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.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 12, 2017 at 11:04 AM
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
0