exclude several values with one condition

I have a list of values to sum, but I need to exclude some of them. My exclusion criteria is something like: if the account number is 1,2,3,4,5 or 6, exclude; otherwise, don't exclude.

The formula involves an external link, and the directory structure can be kind of deep, so when I first wrote it as a series of sumifs (sum(all) - sumif(1) - sumif(2) -sumif(3), etc.), I got a "formula too long" error the next time I tried to update the link.

I changed the formula to a sumproduct structure, and it shortened it quite a bit, but it's still a bit long, and I'm worried it may give me trouble again. I'm also just not happy with it. I'd much rather have one condition than 7 of them for my exclusion.

I haven't been able to find any function that would allow me to create a logical condition in Excel along the lines of:

value in (1,2,3,4,5,6)

The closest I came was to use vlookup and test for #N/A error with the ISNA function, but I can't get it to work for an array, and I'm trying to get one result (a sum), not an array of results.

So, to sum up the problem, I have an external workbook with two columns of interest: the account number and the amount. I want the sum of all the amounts, except for those in certain account numbers. If I write the exclusions as a series of conditions, my formula becomes too long, so I'm looking for a way to build one condition in my sumproduct formula that checks each value in an array against not one value, but a list of value, returning TRUE whenever the tested value is part of the condition list.

I think I'm looking for a way to duplicate the "element of" logical operator, as in 3 element of (1,2,3,4) returns TRUE but 6 element of (1,2,3,4) returns FALSE.

I know about the OR function, but to use it, I still have to link the external workbook in each condition within the OR function, and on top of that, I'm having trouble getting to work for an array.

Can anyone help?

Brigitte

Try

=SUMPRODUCT(--ISNA(MATCH(account_range,{1,2,3,4,5,6},0)),amount_range)

Exactly what I was looking for!

I couldn't get the syntax right between match and sumproduct.

Thanks!