software

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.One way:

=SUM(B1:B4)-SUMIF(A1:A4,quot;aquot;,B1:B4)-SUMIF(A1:A4,quot;bquot;,B1:B4)

--
HTH

Sandy
with @tiscali.co.ukquot;Goosemanquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; I have two columns eg:
gt;
gt; a 1
gt; b 2
gt; c 3
gt; d 4
gt;
gt; How do I sum all the values on the right column if the column on the
gt; left does not equal (for example) a and b - leading to 7?
gt;
gt; Ideally, the data of things not to include shoud be a range.
gt;
gt; Many thanks in advance.
gt;

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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=529022=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A1:A4,K1:K2,0)))),B1:B4)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Goosemanquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; I have two columns eg:
gt;
gt; a 1
gt; b 2
gt; c 3
gt; d 4
gt;
gt; How do I sum all the values on the right column if the column on the
gt; left does not equal (for example) a and b - leading to 7?
gt;
gt; Ideally, the data of things not to include shoud be a range.
gt;
gt; Many thanks in advance.
gt;