SUMIF function

[FLKulchar]

How do I sum a column of numbers if I ONLY wish to sum
the numbers that are all BUT 2 or 4??
For example, my A column array has the following numbers

A1 5
A2 2
A3 2
A4 4
A5 6
A6 8
A7 7
A8 9
A9 4
A10 6

The =SUM function returns 53 as a value.

The =SUMIF(A1:A10,2) returns 4 as a value.
The =SUMIF(A1:A10,4) returns 8 as a value.

HOW DO I COMBINE THE LAST TO SUMIF's using, perhaps,
an "OR" formula, or WHATEVER.

This does NOT work: =SUMIF(A1:A10,"OR(2,4)")

PLEASE HELP!!

MUST i use =sum (ALL)-sumif (2)- sumif (8)

thanks,

Larry
..

 

[Dana DeLouis]

This does NOT work: =SUMIF(A1:A10,"OR(2,4)")

The problem with something like "OR(2,4)" would be done similar to
"=OR(Rng={2,4})"
However, this generates an n*m array, so you would not be saving any typing
by doing it this way. (the m being 2 here for 2 & 4). You generate a column
for each comparison number.

Just one way using SUMPRODUCT might be something like this, with Rng being
your range. Here, you can easily expand the 2&4 to include other numbers if
you wish.

=SUMPRODUCT(0+ISNA(MATCH(rng,{2,4},0)),rng)

HTH.