Excel Formula Help

steves100 14:14 08 Jan 2006
Locked

Please can anyone help with excel formulas say I have this formula
=COUNTIF(A1:F1,"M")*8
but I need the formula to only apply to one or two columns in the same row say A1 and C1 need to return the number *8 but columns B1 and D1 need to return the number *4
and then the results added together as one answer 8+4+8+4=24 Can the be written in one formula?
Steve.

VoG II 14:20 08 Jan 2006

=(A1="M")*8+(B1="M")*4+(C1="M")*8+(D1="M")*4

steves100 14:45 08 Jan 2006

Thanks VoG
Its So Simple when you know how been pulling my hair out for the last hour or so did not think of adding (=)
Thanks Again
Steve.

VoG II 14:52 08 Jan 2006

because the formula =(A1="M") will return TRUE or FALSE. However, internally Excel stores these as 1 and 0 respectively. By using the multiplication, you are forcing Excel to produce a numeric result. This is called coercion.

In this case you wanted to do a multiplication anyway. But if you just wanted to display the results of (A1="M") as a number then the ways of doing it (in decreasing order of computational efficiency) are

=--(A1="M")

=(A1="M")+0

=(A1="M")*1

The most common use of coercion is when using the SUMPRODUCT function.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials