Can I have two conditions in a SUMIF?
 

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?

Can I have two conditions in a SUMIF?
 

You can use SUMPRODUCT to test for more than one condition.

=SUMPRODUCT(--(B6:B9999<"F"),--(G6:G9999=0),H6:H9999)

HTH
Elkar


"trant" wrote:

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?

Can I have two conditions in a SUMIF?
 

Hi, thanks for the reply!

Unfortunately this formula does not seem to work, it is resulting in a 0
when I plugged it in, was I supposed to modify it somehow? It's references
seem to check out and match everything on my data...

"Elkar" wrote:

You can use SUMPRODUCT to test for more than one condition.

=SUMPRODUCT(--(B6:B9999<"F"),--(G6:G9999=0),H6:H9999)

HTH
Elkar


"trant" wrote:

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?

Can I have two conditions in a SUMIF?
 

Hi,

I'm confused but try this

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMPRODUCT((G6:G99 9=0)*(B6:B999<"f")*(H6:H999))

Mike

"trant" wrote:

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?

Can I have two conditions in a SUMIF?
 

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(B6:B9999<"F",IF(G6:G9999,G6:G9999,H6:H999 9)))

Hope this helps!

http://www.xl-central.com

In article ,
trant wrote:

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?

Can I have two conditions in a SUMIF?
 

Ok, after re-reading your post, I misunderstood what you were asking for.
Try this instead:

=SUMPRODUCT(--(B6:B9999<"F"),G6:G9999)+SUMPRODUCT(--(B6:B9999<"F"),--(G6:G9999=0),H6:H9999)

HTH
Elkar


"trant" wrote:

Hi, thanks for the reply!

Unfortunately this formula does not seem to work, it is resulting in a 0
when I plugged it in, was I supposed to modify it somehow? It's references
seem to check out and match everything on my data...

"Elkar" wrote:

You can use SUMPRODUCT to test for more than one condition.

=SUMPRODUCT(--(B6:B9999<"F"),--(G6:G9999=0),H6:H9999)

HTH
Elkar


"trant" wrote:

OK Thanks to the help of members of this board I learned to use SUMIF to sum
the values of a column based on a condition... but what if I have multiple
conditions?

Here's my complicated scenario...

I have columns B, G and H represented here.

I am trying to sum up all the values of columns G & H based on certain
conditions.

=SUMIF(B6:B9999,"<F",G6:G9999)+SUMIF(G6:G9999,"0" ,H6:H9999)

First, ignore the entire column if value of B is "F", so that's my first
SUMIF.

Then my second SUMIF is supposed to be if the value of G is 0, then add the
value of H to the sum (otherwise always ignore H)

But it fails when the value of column B is "F" AND the value of G is 0,
because then it includes the value of H thanks to my second SUMIF.

How do I either ignore the second SUMIF if the condition of my first SUMIF
is false or how to I have multiple conditions in my second SUMIF to basically
say if G is 0 AND B < "F" ?