Creating formula in Excel

[nordenvillain]

I am trying to create a formula in Excel that gives a total of ratings that can then be divided to give an average rating. Please see screenshot of information :

A	B	C	D	Total	Total Seedings	Av Score
	3			#VALUE!	3	#VALUE!

I wish to create a formula that multiplies every A rating x 4, every B rating x 3, every C rating x 2 and every D rating x 1. In this case, column A is K, B is L, C is M, D is N, Total of rating scores is O, Total Seedings is P, Av Score is Q and Line is 4.

The formula that I have tried to create is :

=SUM((K4*4)+(L4*3)+(M4*2)+(N4*1))

But this returns a message #ERROR!

In this example using the formula (Which I used last year with no problems and have tried copying and pasting but getting the same error message), Cell O4 should be 9, P4 3 and Q4 3.00

Can you help to get the correct formula to be used ?

Thanks

[#2]

@nordenvillain 

Sorry error message is #VALUE! not #ERROR!

[#3]

@nordenvillain 

Most probably there is a text at least in one of the cells instead of number. But better to check sample file.

[#4]

@nordenvillain 

There may also be spaces, commas, text, special characters among the data which might be causing the issue.

[#5]

@AshaKantaSharma 

Is that all not a text?

[#6]

Hello @Sergei Baklan 

Does Excel considers them all as text ? 

[#7]

@AshaKantaSharma 

Perhaps some of the blank cells are not empty but contain the null string (""), e.g. as a result of formulas in those cells? If so, use:

=SUMPRODUCT(K4:N4,{4,3,2,1})

Though note that if you are not using an English language-version of Excel then the separator within the array constant in the above may need amending.

Regards

[#8]

@AshaKantaSharma 

You may check by ISTEXT()