Using Sumifs combined with an "Or"
 

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

Using Sumifs combined with an "Or"
 

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)

"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

Using Sumifs combined with an "Or"
 

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

Using Sumifs combined with an "Or"
 

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

Using Sumifs combined with an "Or"
 

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