Formulas

[Raymond]

=SUM(IF(ISTEXT(D6:S6),1,0))

Whatever the result of the above formulas, I want to minus 1 to that result,
How do I put the syntax so it will minus 1 unit from the result?

Thanks

 

[Frank Kabel]

=SUM(IF(ISTEXT(D6:S6),1,0))-1

 

[Raymond]

What happen is that several cells on the left will return a value of 1 and
add to the formulas below anytime I enter any text. However, I follow your
formulas syntax, and no matter how many cell I entered the text, value for
that formulas is always added up to -1. I wonder what is wrong.

 

[Frank Kabel]

Hi
what does the SUM/IF formula return?. Also have you entered the formula
as array formula?

 

[Raymond]

what does the SUM/IF formula return?. Also have you entered the formula
as array formula?
return a 1 if there are text in the cells that need to be added on the left,
and return a 0 if there is no text.

=SUM(IF(ISTEXT(D6:S6),1,0),-1)

I played around and found the above formulas works, but if there is no text
enter, the formulas cell will always display a -1, which is not good.

 

[Frank Kabel]

Hi
you have to enter this formula as array formula. Enter it with
CTRL+SHIFT+ENTER.

And of yourse if non of these cells contains a text entry the formula
returns '-1'. What would be your expected result

 

[Raymond]

Hi
you have to enter this formula as array formula. Enter it with
CTRL+SHIFT+ENTER.

And of yourse if non of these cells contains a text entry the formula
returns '-1'. What would be your expected result
My expected result if none of the cells contain a text entry would be a
result of 0.

How do I enter the formulas with CTRL+SHIFT+ENTER. can you walk with me
step by step?

Many many thanks