 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008SumIf 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? =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green"))  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello, > [quoted text clipped - 26 lines] > sum > of those criterias? Bit of overkill =SUMPRODUCT(A2:A20,--(B2:B20="Spring"),--(C2:C20="Green")) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > =SUMPRODUCT(--(A2:A20),--(B2:B20="Spring"),--(C2:C20="Green")) > [quoted text clipped - 28 lines] >> sum >> of those criterias? 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. > Bit of overkill > [quoted text clipped - 32 lines] > >> sum > >> of those criterias? =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink")) -- David Biddulph > Unfortunately this did not assist me in getting a subset of totals based > on [quoted text clipped - 42 lines] >> >> sum >> >> of those criterias? 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")+(G1004: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")) > =SUMPRODUCT(A2:A20,(B2:B20="Spring")+(B2:B20="Fall"),(C2:C20="Green")+(C2:C20="Pink")) > -- [quoted text clipped - 46 lines] > >> >> sum > >> >> of those criterias? 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 > This actually worked for me once. > [quoted text clipped - 78 lines] >> >> >> sum >> >> >> of those criterias? 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. > In your "second time" formula you haven't coerced the > ($G1004:$G1053="Bananas") term from Boolean to a number. You either need a [quoted text clipped - 88 lines] > >> >> >> sum > >> >> >> of those criterias? =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC of GA / ABC of MA","XYZ"})) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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. [quoted text clipped - 109 lines] >> >> >> >> sum >> >> >> >> of those criterias? 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. > =SUMPRODUCT(($A1004:$A1053)*($G1004:$G1053="Bananas")*($J1004:$J1053={"ABC > of GA / ABC of MA","XYZ"})) [quoted text clipped - 112 lines] > >> >> >> >> sum > >> >> >> >> of those criterias? 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="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC 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$1053="Bananas")+($G$1004:$G$1053="123"),($J$1004:$J$1053="ABC of GA / ABC of MA")+($J$1004:$J$1053="1,2,3"))) Please???? > Hmmmmm. . . . > [quoted text clipped - 121 lines] > > >> >> >> >> sum > > >> >> >> >> of those criterias? 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 > Wow! > [quoted text clipped - 169 lines] >> > >> >> >> >> sum >> > >> >> >> >> of those criterias? 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? > 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 [quoted text clipped - 184 lines] > >> > >> >> >> >> sum > >> > >> >> >> >> of those criterias? You are obviously having difficulty in reading the replies you have been given. -- David Biddulph > Oh, cool. > [quoted text clipped - 230 lines] >> >> > >> >> >> >> sum >> >> > >> >> >> >> of those criterias? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.