In Excel taking number or text as 10 in other cell

[Gaurav Patel]

I want to create a excel sheet in which there will be some column which will get the input in alphabets and numeric values. The last column will be for total. Irrespective of any alphabets and numeric values in the backend the excel should take 10 by default and if the cell is left blank then it should take value as 0. So in the total it should add all cell.

Ex: A B C D Total
SAM 34 yes 30 (10+10+0+10)
OR
JAM 43 45 30 (10+0+10+10)


So, That means if i entered any value in cell it should take 10 by default at back-end and if left blank it should take 0 by default at back-end and should add all cell and give output of all input data in cell. Like above example and attached sheet.

[Luuk]

you need an array function for that
(see: http://alturl.com/jhqkc)

In your example, with data in C3:F3, next function:
=SUM(IF(ISBLANK(C3:F3);0;10))
After the function press not only ENTER, but SHIFT+CTRL+ENTER

[Killer42]

Just a note: the arguments in the IF function are separated by commas, not semicolons. (Unless that's a user-defined setting in Excel, perhaps).

[Killer42]

It might also be a good idea to explore functions like COUNTIF. I think you could use that to count the non-blank cells in a range, then multiply the result by 10.

[Luuk]

@Killer42: the semicolon is a country-specific setting.

In the Netherland we have a decimal comma, therefore a comma as separator in a function would not be practical ;)

[Killer42]

Thanks, I knew different countries used different decimals and so on, but never knew that the function syntax varied like that.