Using IF Statements in Excel with SUM formula

I have been having great difficulty in getting my formulae to work in Excel.

Basically I have an IF statement, like this:

In cell U5 :
=IF(K5="text","1","")

I had copied these all down to cell U500.

I then wanted to use SUM to combine all the numbers, so that I have a figure afterwards, like this:

In cell S5 :
=SUM(U5:U500)

I am getting a "0" in cell S5.

Why is this happening? I definitely have text in K column.

1 Answer

The issue is you're adding 1 to Excel as a string, not a number

You have

=IF(K5="text","1","")

You need

=IF(K5="text",1,"")

Maybe having

=IF(K5="text",1)

is better, or

=IF(K5="text",1,0)

but depends on how you're using it else where.

Screen to prove it won't work if you add the number as a string (note in the image, the 5 in the formula is a string ("5")