|
SumIf formula from one column based on criteria from two other col
Hello,
I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other col
=SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green"))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other col
Bit of overkill
=SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
Unfortunately this did not assist me in getting a subset of totals based on
the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
=SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink"))
-- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
This actually worked for me once.
However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
In your "second time" formula you haven't coerced the
($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
Wow! This is really cool - only the sumtotal was inaccurate - instead of
giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
=SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC
of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
Hmmmmm. . . .
I tried it but this time it gave me a value error message. the other way worked, but was just inaccurate. Any other ideas on how to put forth the math? thanks for all your help guys. "Bob Phillips" wrote: =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
Wow!
I finally got the formula to work in all aspects of the data I was trying to extract! And it's now accurate - only I had to trick it. Here's what I did: Since it had to include a boolean operation when there was only one category to find, I created a false one and told it to add it each time (of course it would add '0' because there was not category in the range called "1,2,3": =SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bana nas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="AB C of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")) Now I need help in counting each time that data occures. I realize there is no =CountProduct operation. I tried this but it didn't work - I think I am close - can anyone help me? =Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1 053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J $1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))) Please???? "stanasia" wrote: Hmmmmm. . . . I tried it but this time it gave me a value error message. the other way worked, but was just inaccurate. Any other ideas on how to put forth the math? thanks for all your help guys. "Bob Phillips" wrote: =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of
adding zero. You could have just used +0 (or *1) but the usual recommendation is to use the double unary minus so you could have had ... ,--($J$1004:$J$1053="ABC of GA / ABC of MA") To answer your second question, if you don't want to add up the values in column A, leave that term out. =SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$ G$1053="123"),--($J$1004:$J$1053="ABC of GA / ABC of MA")) or =SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004: $G$1053="123"))*($J$1004:$J$1053="ABC of GA / ABC of MA")) -- David Biddulph "stanasia" wrote in message ... Wow! I finally got the formula to work in all aspects of the data I was trying to extract! And it's now accurate - only I had to trick it. Here's what I did: Since it had to include a boolean operation when there was only one category to find, I created a false one and told it to add it each time (of course it would add '0' because there was not category in the range called "1,2,3": =SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bana nas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="AB C of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")) Now I need help in counting each time that data occures. I realize there is no =CountProduct operation. I tried this but it didn't work - I think I am close - can anyone help me? =Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1 053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J $1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))) Please???? "stanasia" wrote: Hmmmmm. . . . I tried it but this time it gave me a value error message. the other way worked, but was just inaccurate. Any other ideas on how to put forth the math? thanks for all your help guys. "Bob Phillips" wrote: =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
Oh, cool.
I didn't know I could have used '0' - but that makes a lot of sense. I tried the double unary minus, but the totals were wrong when I did that, and sometimes it returned '0' for the total. I'm going to go back and change the "1,2,3" for '0'. That will make it less confusing for anyone else that may need to change it in the future. As for my counting formula, what to do about that one? How can I do basically the same thing but count the number of times each item occurs instead of the total amount of numerical value for each occurance. i.e., 4 apples, and 6 bananas, and 3 more apples that are matching the other criteria of ="ABC of GA / ABC of MA". I don't need the sum this time. Just the fact that apples happened 2 times and bananas happened 1 time. Is there a twist on this that includes Countif? "David Biddulph" wrote: It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of adding zero. You could have just used +0 (or *1) but the usual recommendation is to use the double unary minus so you could have had ... ,--($J$1004:$J$1053="ABC of GA / ABC of MA") To answer your second question, if you don't want to add up the values in column A, leave that term out. =SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$ G$1053="123"),--($J$1004:$J$1053="ABC of GA / ABC of MA")) or =SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004: $G$1053="123"))*($J$1004:$J$1053="ABC of GA / ABC of MA")) -- David Biddulph "stanasia" wrote in message ... Wow! I finally got the formula to work in all aspects of the data I was trying to extract! And it's now accurate - only I had to trick it. Here's what I did: Since it had to include a boolean operation when there was only one category to find, I created a false one and told it to add it each time (of course it would add '0' because there was not category in the range called "1,2,3": =SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bana nas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="AB C of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")) Now I need help in counting each time that data occures. I realize there is no =CountProduct operation. I tried this but it didn't work - I think I am close - can anyone help me? =Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1 053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J $1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))) Please???? "stanasia" wrote: Hmmmmm. . . . I tried it but this time it gave me a value error message. the other way worked, but was just inaccurate. Any other ideas on how to put forth the math? thanks for all your help guys. "Bob Phillips" wrote: =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
SumIf formula from one column based on criteria from two other
You are obviously having difficulty in reading the replies you have been
given. -- David Biddulph "stanasia" wrote in message ... Oh, cool. I didn't know I could have used '0' - but that makes a lot of sense. I tried the double unary minus, but the totals were wrong when I did that, and sometimes it returned '0' for the total. I'm going to go back and change the "1,2,3" for '0'. That will make it less confusing for anyone else that may need to change it in the future. As for my counting formula, what to do about that one? How can I do basically the same thing but count the number of times each item occurs instead of the total amount of numerical value for each occurance. i.e., 4 apples, and 6 bananas, and 3 more apples that are matching the other criteria of ="ABC of GA / ABC of MA". I don't need the sum this time. Just the fact that apples happened 2 times and bananas happened 1 time. Is there a twist on this that includes Countif? "David Biddulph" wrote: It was pointless to include your +($J$1004:$J$1053="1,2,3") as a means of adding zero. You could have just used +0 (or *1) but the usual recommendation is to use the double unary minus so you could have had ... ,--($J$1004:$J$1053="ABC of GA / ABC of MA") To answer your second question, if you don't want to add up the values in column A, leave that term out. =SUMPRODUCT(($G$1004:$G$1053="Bananas")+($G$1004:$ G$1053="123"),--($J$1004:$J$1053="ABC of GA / ABC of MA")) or =SUMPRODUCT((($G$1004:$G$1053="Bananas")+($G$1004: $G$1053="123"))*($J$1004:$J$1053="ABC of GA / ABC of MA")) -- David Biddulph "stanasia" wrote in message ... Wow! I finally got the formula to work in all aspects of the data I was trying to extract! And it's now accurate - only I had to trick it. Here's what I did: Since it had to include a boolean operation when there was only one category to find, I created a false one and told it to add it each time (of course it would add '0' because there was not category in the range called "1,2,3": =SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1053="Bana nas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="AB C of GA / ABC of MA")+($J$1004:$J$1053="1,2,3")) Now I need help in counting each time that data occures. I realize there is no =CountProduct operation. I tried this but it didn't work - I think I am close - can anyone help me? =Countif(=SUMPRODUCT($A$1004:$A$1053,($G$1004:$G$1 053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J $1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))) Please???? "stanasia" wrote: Hmmmmm. . . . I tried it but this time it gave me a value error message. the other way worked, but was just inaccurate. Any other ideas on how to put forth the math? thanks for all your help guys. "Bob Phillips" wrote: =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Banana s")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Wow! This is really cool - only the sumtotal was inaccurate - instead of giving me the correct number of 24 - it gave me the wrong total of 59. I used the * function instead of the , between array 2 & 3. When I used the - - - it gave me a number in the 220's. How can I use this formula to get the right number? Thanks for the lesson - you really know your stuff! now if I can just get it right. "David Biddulph" wrote: In your "second time" formula you haven't coerced the ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a multiply instead of the comma separating the arguments in the SUMPRODUCT expression, or otherwise the double unary minus construct. [In the "first time" formula you've done arithmetic which forces the Boolean to a numeric value.] -- David Biddulph "stanasia" wrote in message ... This actually worked for me once. However, it didn't the second time I tried to use it in another cell to come up with the statistical totals I was looking for. Is there any reason why it would work once and not again? The only difference is that the first time there were multiple criteria in the second array and in the second time there was only one reference in the second array. The first and third array's remained identical and didn't change. I checked and double checked the total on the first time and it was accurate. Can you see what I did and tell me what I did wrong the second time? i.e., the two formulas are below: First time - it worked fine: =SUMPRODUCT(A1004:A1053,(G1004:G1053="Apples")+(G1 004:G1053="Oranges")+(G1004:G1053="ABC of VA"),(J1004:J1053="ABC of GA / ABC of MA")+(J1004:J1053="XYZ")) Second time: =SUMPRODUCT($A1004:$A1053,($G1004:$G1053="Bananas" ),($J1004:$J1053="ABC of GA / ABC of MA")+($J1004:$J1053="XYZ")) "David Biddulph" wrote: =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall "),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph "stanasia" wrote in message ... Unfortunately this did not assist me in getting a subset of totals based on the other criteria in the second and third column. What I need to do is compare criteria from column B against criteria in Column C and then sum those adjacent Column A amounts. But I am having trouble writing this formula. "Bob Phillips" wrote: Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stanasia" wrote in message ... Hello, I am knowledgable how to get a SumIf from one column based one or more criterias from only one other column. However, now I need to compare criteria from two different columns and return the SumIf from a third column. I tried Concantenation, but to no avail and even tried nesting If functions but still no avail. Can anyone help? Below is the scenario: Col A Col B Col C 52 Winter Pink 23 Spring Pink 6 Fall Green 10 Winter Blue 30 Summer Green 10 Fall Blue 5 Spring Green 45 Winter Green 80 Fall Pink 12 Summer Pink 34 Summer Blue Now how do I return the sum of Col A for all criteria that meets Spring = Green or Pink + Fall = Green or Pink based on Col's B & C?. And only the sum of those criterias? |
| All times are GMT +1. The time now is 03:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com