N/A

[Steved]

Hello from Steved

Please if a cell has in it "N/A" What is require in the bootom formula to
reconize thia please as at it is at the moment it returns value in the sum
column, meaning I have to change it from N/A to 0 for the formula to work
thanks.

=SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9)

 

[Bob Phillips]

If you actually mean #N/A, then it is ISNA or ISERROR, or maybe conversely
test for ISNUMBER for validity.

You formula will not work

=SUMPRODUCT(--('From Charters'!$A$9=$A$41)*'From Charters'!$D$9:$E$9)

as the ranges must be the same size, perhaps you really mean

=SUMPRODUCT(--('From Charters'!$A$9:$B$9=$A$41)*'From Charters'!$D$9:$E$9)

and you don't need the first --

Test that with

=SUMPRODUCT(('From Charters'!$A$9:$B$9=$A$41))*(ISNUMBER('From
Charters'!$D$9:$E$9))

But this is all guesswork.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Biff]

Try this:

=('From Charters'!$A$9=$A$41)*SUM('From Charters'!$D$9:$E$9)

Biff

 

[Steved]

Thankyou both It works as I intended.

Cheers

Try this:

=('From Charters'!$A$9=$A$41)*SUM('From Charters'!$D$9:$E$9)

Biff