Sumproduct #N/A

[Peter B]

I have a problem with my sumproduct funtion.
Note that I am using a Norwegian keyboard, so the ","
is ";" on my computer.

=SUMPRODUCT((LEFT(Data!$A$2:$A$1276;1)={"F";"M"})*(Data!
$U$2:$U$1276))

I'll get a #N/A error for this function.

I tried to make a dummy Sumproduct function and this one
worked perfectly.

=SUMPRODUCT((LEFT(B128:B129;1)={"D";"T"})*(C128:C129)).
I have also tried to remove the

If I only keept the "F" to look for it also workes ok,
=SUMPRODUCT((LEFT(Data!$A$2:$A$1276;1)="F")*(Data!
$U$2:$U$1276))

Anyone have any answers to why this happens?

Rgds,

Peter B

 

[Arvi Laanemets]

Hi

I tried, and I get always error when I used array in comparision. So maybe
you simply use the formula:
=SUMPRODUCT((LEFT(Data!$A$2:$A$1276;1)="F")*(Data!$U$2:$U$1276))+SUMPRODUCT(
(LEFT(Data!$A$2:$A$1276;1)="M")*(Data!$U$2:$U$1276))

 

[Bob Phillips]

Peter,

Try this

=SUMPRODUCT((NOT(ISNA(LEFT(Data!$A$2:$A$1276,1)={"F";"M"})))*(Data!$U$2:$U$1
276))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

You probably have #N/A's in (one of) the ranges of interest.

If you clean up the ranges from such errors, you can also have:

=SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(Data!$A$2:$A$1276;1),{"F";"M"},0))),Data!
$U$2:$U$1276)

which will be insensitive to #N/A's in A-range (but not to those in
U-range).

If you can't avoid #N/A's, you have to switch to...

=SUM(IF(ISNUMBER(MATCH(LEFT(Data!$A$2:$A$1276;1),{"F";"M"},0)*Data!$U$2:$U$1
276),Data!$U$2:$U$1276))

which must be confirmed with control+shift+enter instead of just with enter.

 

[Bob Phillips]

Sorry, used UK separators.Should be

=SUMPRODUCT((NOT(ISNA(LEFT(Data!$A$2:$A$1276;1)={"F";"M"})))*(Data!$U$2:$U$1
276))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)