From Excel HELP on Value#:

"Microsoft Excel cannot translate the text into the correct data type. Make
sure the formula or function is correct for the required operand or
argument, and that the cells that are referenced by the formula contain
valid values. For example, if cell A5 contains a number and cell A6 contains
the text "Not available", the formula =A5+A6 will return the error #VALUE!."

A simple exercise to show how Excel sees cells formatted as text differently
from numbers:

Enter '1 in cell A1 (with the single quote)

Enter =A1=1 in B1

Enter =A1+0=1 in C1


Explanation:
- The value you entered into A1 is text
- Excel knows a text value does not equal a number
- Performing a mathematical operation on a 'text' number changes it to a
number.

--
Steve

wrote in message
...
On 14 Aug, 16:25, Pete_UK wrote:
Martin,

take a look here for an in-depth explanation of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete

Thanks Pete, good page. For some reason I'm still not having any
sucess in coercing Excel to treat the TRUE and FALSE arguments as 1
and 0 by using the asterisk operator rather then the comma. I just get
a #VALUE error. However, adding zero is working, although it's not as
neat, so I'm not too worried.