Hi,

I need to use the sumproduct function but I would like to exclude 4
particular items which occur in a certain field

eg
SUMPRODUCT(($A$1:$A$1000 = FT)*($B$1:$B$1000 = 1)*($D$1:$D$1000))
works fine,

but now I need to exclude column C items which = RP, Sel, SP & Com

How do I do this?

I think you may need to put double quotes around FT,RP,Sel,SP,Com if they
are strings. You also need to minus signs (--) in front of each part of the
sumproduct.

=SUMPRODUCT(--($A$1:$A$1000 = FT),--($B$1:$B$1000 =
1),--($D$1:$D$1000),--($C$1:$C$1000<RP),--($C$1:$C$1000<Sel),--($C$1:$C$1000<SP),--($C$1:$C$1000<Com))


"Hilton" wrote:

Hi,

I need to use the sumproduct function but I would like to exclude 4
particular items which occur in a certain field

eg
SUMPRODUCT(($A$1:$A$1000 = FT)*($B$1:$B$1000 = 1)*($D$1:$D$1000))
works fine,

but now I need to exclude column C items which = RP, Sel, SP & Com

How do I do this?

=SUMPRODUCT((A1:A100="FT")*(B1:B100=1)*(ISNA(MATCH (C1:C100,{"RP","Sel","SP","Com"},0)))*D1:D100)



"Hilton" wrote:

Hi,

I need to use the sumproduct function but I would like to exclude 4
particular items which occur in a certain field

eg
SUMPRODUCT(($A$1:$A$1000 = FT)*($B$1:$B$1000 = 1)*($D$1:$D$1000))
works fine,

but now I need to exclude column C items which = RP, Sel, SP & Com

How do I do this?