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.
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
The most common use of coercion is when using the SUMPRODUCT function.
This thread is now locked and can not be replied to.