Using Sumifs combined with an "Or"

[Guest]

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

 

[Bob Phillips]

Really "1"?

=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)

 

[Bob Phillips]

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)

 

[Guest]

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]

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)