Excel Formula Help

  steves100 14:14 08 Jan 06
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 06

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

  steves100 14:45 08 Jan 06

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 06

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.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…