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.

One way:

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

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"Gooseman" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

Try

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

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

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Gooseman" <[email protected]> wrote in message
news:[email protected]...
> 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.
>