Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with 2 conditions | Excel Worksheet Functions | |||
Using sumif with two conditions | Excel Discussion (Misc queries) | |||
SUMIF with 2 conditions | Excel Worksheet Functions | |||
sumif more conditions | Excel Worksheet Functions | |||
sumif with two conditions | Excel Discussion (Misc queries) |