You can add an OR condition.

=SUMPRODUCT(--($B$2:$B$2000=1),--((($D$2:$D$2000=6400)*($D$2:$D$2000<=6414))+(LEFT ($D$2:$D$2000,2)="KA")),$I$2:$I$2000)

This alos gives another way of testing the array of values.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mike_vr" wrote in message
...
Hi Bob,

Thanks for the help so far. The Sumproduct works perfectly for the range
of
numbers (6400 - 6414), but then there is the curve ball of anything
starting
with KA (this is then usually followed by a five digit number eg KA20005
or
KA20061 etc).

I've tried using KA* after the 641

=SUMPRODUCT(--($B$2:$B$20=1),--(ISNUMBER(MATCH($D$2:$D$20,{640,641,KA*},0))),$I$2 :$I$20)

But this comes back with an error message

Is there a way to also select those things starting with KA? If it helps,
the remainder of choices are all text.

Thanks again,

Mike
"Bob Phillips" wrote:

I now thin it should be

=SUMPRODUCT(--($B$2:$B$20=1),--(ISNUMBER(MATCH($D$2:$D$20,{640,641},0))),$I$2:$I$ 20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"mike_vr" wrote in message
...
Hi all,

Wonder if anyone can help me. Using Excel 2007 and the SumIfs function.
I
have my sum range, and my first criteria range

=SUMIFS($I$2:$I$2000,$B$2:$B$2000,"1",

i.e. sum anything in column I that equals to a one from column B.

But for my second criteria range $D$2:$D$2000,the value can be one of
14
possibilities 6400, 6401, 6402 up to 6412 as well as anything beginning
with
KA.

So basically I'm trying to find out if there's a way to sum something
if
it
equals a 1 in the one column, and then a range of possibilities in
another
column after that.

Is this possible? Any help would be appreciated!

Thanks,

Mike