Array sum for negative match
 

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.

Array sum for negative match
 

One way:

=SUM(B1:B4)-SUMIF(A1:A4,"a",B1:B4)-SUMIF(A1:A4,"b",B1:B4)

--
HTH

Sandy

with @tiscali.co.uk


"Gooseman" wrote in message
oups.com...
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.

Array sum for negative match
 

Try

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529022

Array sum for negative match
 

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Gooseman" wrote in message
oups.com...
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.