Array sum for negative match

G

Gooseman

Hi,

I have two columns eg:

a 1
b 2
c 3
d 4

How do I sum all the values on the right column if the column on the
left does not equal (for example) a and b - leading to 7?

Ideally, the data of things not to include shoud be a range.

Many thanks in advance.
 
D

daddylonglegs

Try

=SUMPRODUCT(1-ISNUMBER(MATCH(A1:A4,F1:F10,0)),B1:B4)

where F1:F10 is a range containing the items not to include
 
B

Bob Phillips

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A1:A4,K1:K2,0)))),B1:B4)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top