If to sum function.

R

Raymond

I created a cell so that everytime I enter a text from some other cells, it
will add 1 to the value of the cell, otherwise, it won't add anything. If I
enter no text or a number in the other cells, this cell will display 0. The
formulas for the cell is like this: =SUM(IF(ISTEXT(D13:S13),1,0))

After entering text to three other cells, this cell will display a value of
3. However, I want that valued to be 1 less than. I cannot do this. If I
put minus 1 with an if statement, this cell will display a value of -1 when
no text is entered in other cells. Any folks have any idea how to fix this?

Thanks
 
T

Tom Ogilvy

=Max(SUM(IF(ISTEXT(D13:S13),1,0))-1,0)

Entered with Ctrl+Shift+enter will show 0 for all empty or text in only one
cell.
 
R

Raymond

I am sorry, I don't get it. Enter what with Ctrl+shift+enter? The
formulas? The text into other cells? I can't do neith of the above while
hold down ctrl+shift+enter.

Thanks.
 
M

Myrna Larson

Normally, when you type in a formula, you press ENTER when you've finished it.
With an array formula you must press CTRL+SHIFT+ENTER. Check Help for array
formulas for more information.
 
F

Frank Kabel

Hi
enter Tom's formula in the formula bar. but then instead of committing
this formula with a single ENTER press the keys CTRL+SHIFT+ENTER.
Afterwards the formula should be shown enclosed with curly brackets
 
T

Tom Ogilvy

how did you enter your original formula? It requires that it be entered
with Ctrl+shift+enter as well. Hopefully Frank's explanation has cleared it
up.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top