 |
 | 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 / March 2008sumif with 3 condition - urgent
Hi all I have protect excel sheet1, which we have enter data in many column. I have create another sheet2 for summery. here we have use 2 criteria like Buyer and mode (ship or air) its working fine. C D E buyer air_qty sea_qty XX 100 200 In Column D =SUM(IF('data'!$F$6:$F$115=C2,IF('data'!$AX$6:$AX$115="Air",'data'!$L$6:$L$115,0),0)) In Column E =SUM(IF('data'!$F$6:$F$115=C2,IF('data'!$AX$6:$AX$115="Sea",'data'!$L$6:$L$115,0),0)) Its working fine. now I want add another criteria like PM in column B (Production Manager) and in data sheet PM column is E. condition 1. If pm is Nill then sum qty buyerwise and mode wise respectively condition 2. if Pm is given and buyer is null then sum Qty PM and Mode wise respectively condition 3. if Pm is given and buyer is given then sum of quantity PM, buyer and Mode wise B C D E pm buyer air_qty sea_qty XX 100 200 how can change formula or any other ways please help me Thanks in advance RKS You can use SUMPRODUCT - the general form is: =SUMPRODUCT((condition_1)*(condition_2)*(condition_3)*(range_to_sum)) so your first formula could be written as: =SUMPRODUCT(('data'!$F$6:$F$115=C2)*('data'!$AX$6:$AX $115="Air")*('data'!$L$6:$L$115)) Perhaps you can see how to extend this to suit your new requirements. Hope this helps. Pete > Hi all > [quoted text clipped - 29 lines] > Thanks in advance > RKS Thanks Pete_uk for ur reply. Its working but result are wrong. please see my condition 1. if condition_1 and condition_2 is null or blank then it will calculate sum of whole qty (range_to_sum) with condition_3 only. means if any condition is blank or null it will not consider. DATA SAMPLE pm buyer mode qty Condition_1 = Pm Condition_2 = buyer AA XX AIR 100 Condition_3 = mode BB YY AIR 100 AA YY AIR 100 CC XX AIR 100 AA XX SHIP 100 if Condition_1 is null, condition_2=XX and condition_3 is null Result is 300 if Condition_1 is AA, condition_2=XX and condition_3 is null Result is 200 if Condition_1 is AA, condition_2=XX and condition_3 is AIR Result is 100 I THINK NOW ITS CLEAR. PLEASE HELP ME. IF ANY OTHER WAY TELL ME. THANKS RKS > You can use SUMPRODUCT - the general form is: > [quoted text clipped - 44 lines] > > Thanks in advance > > RKS Normally when you want to do a conditional sum you want all the conditions satisfied, i.e. IF Pm="AA" AND Buyer="XX" AND mode="AIR" THEN add the corresponding Qty. You would have a formula like this (based on your sample): =SUMPRODUCT((A2:A5="AA")*(B2:B5="XX")*(C2:C5="AIR")*(D2:D5)) This would give a result of 100. However, you seem to be suggesting that if you don't specify a value for Pm, or Buyer, or Mode, then you want the formula to automatically disregard that condition - is this what you want? Pete > Thanks Pete_uk for ur reply. Its working but result are wrong. please see my > condition [quoted text clipped - 68 lines] > > - Show quoted text - Thanks Pete_uk for reply You are right. I wants that if we don't specify a value for Pm, or Buyer, or Mode, then formula to automatically disregard that condition. I can show u example which we wants like this. all condition is running and give result same which we show u. if Condition_1 is null, condition_2=XX and condition_3 is null Result is 300 if Condition_1 is AA, condition_2=XX and condition_3 is null Result is 200 if Condition_1 is AA, condition_2=XX and condition_3 is AIR Result is 100 thanks and waiting your reply. RKS > Normally when you want to do a conditional sum you want all the > conditions satisfied, i.e. IF Pm="AA" AND Buyer="XX" AND mode="AIR" [quoted text clipped - 83 lines] > > > > - Show quoted text - Well, the easiest way is to just omit that condition from the formula, so that if you are not bothered about the value of Pm or Mode, for example, then the formula is: =SUMPRODUCT((B2:B5="XX")*(D2:D5)) If you want to pick up when Pm = "AA" and Buyer ="XX", then you would have a formula: =SUMPRODUCT((A2:A5="AA")*(B2:B5="XX")*(D2:D5)) If you wanted this to happen in one formula automatically, then you would have to put the values of Pm, Buyer and Mode in 3 cells somewhere and then have the composite formula refer to those cells. It would have several IFs to cover all possible combinations of the 3 variables (or empty). Post back if this is what you really want. Hope this helps. Pete > Thanks Pete_uk for reply > [quoted text clipped - 100 lines] > > - Show quoted text - Once again thanks Pete_uk. You are right. I wants all in One formula. my summary report is like this PM Buyer <------Qty--------> Air Ship Total I can use PM and Buyer dropdown list and wants one formula (with all condition) in air and ship column. so I need one formula. I know its complicated. please help me. RKS > Well, the easiest way is to just omit that condition from the formula, > so that if you are not bothered about the value of Pm or Mode, for [quoted text clipped - 121 lines] > > > > - Show quoted text - Your latest example seems a bit different. Will you have a drop-down for PM and Buyer (and in which cells?), and then you want separate columns for Air and Ship ? That is actually two formulae. Is the summary report on the same sheet or a different sheet? Please specify the exact layout of your data so that the formulae I give you will not need to be modified. Pete > Once again thanks Pete_uk. > You are right. I wants all in One formula. my summary report is like this [quoted text clipped - 135 lines] > > - Show quoted text - Hi Pete_Uk Thanks for your reply once again. I can write formula which i want. i can show u its working fine which i wants. we have 3 condition, you are write we can write 2 formula one for AIR and another for SHIP which we show earlier. Now question is, if my Boss giving me one more condition what i can do, because maximum 6 IF we can use. Have you any other idea please tell me may be we can face this problem earlier. you see my formula then u understand. =IF(ISBLANK(C6),SUM(IF('T&A'!$E$6:$E$115=B6,IF('T&A'!$AW$6:$AW$115=$D$5,'T&A'!$K$6:$K$115,0),0)), IF(ISBLANK(B6),SUM(IF('T&A'!$F$6:$F$115=C6,IF('T&A'!$AW$6:$AW$115=$D$5,'T&A'!$K$6:$K$115,0),0)),SUMPRODUCT(('T&A'!$E$6:$E$115=B6)*('T&A'!$F$6:$F$115=C6)*('T&A'!$AW$6:$AW$115=$D$5)*(T&A'!$K$6:$K$115)))) Thanks > Your latest example seems a bit different. Will you have a drop-down > for PM and Buyer (and in which cells?), and then you want separate [quoted text clipped - 145 lines] > > > > - Show quoted text - OK, I think you have "AIR" in D5 of a different sheet, and your pull- downs for PM and Buyer are in B6 and C6 of that sheet. So, try this formula in D6: =IF(AND($B6="",$C6=""),SUMPRODUCT(('T&A'!$AW$6:$AW$115=D$5)*(T&A'!$K $6:$K$115)),IF($B6="",SUMPRODUCT(('T&A'!$F$6:$F$115=$C6)*('T&A'!$AW $6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMPRODUCT(('T&A'!$E$6:$E $115=$B6)*('T&A'!$AW$6:$AW$115=D$5)*(T&A'!$K$6:$K $115)),SUMPRODUCT(('T&A'!$E$6:$E$115=$B6)*('T&A'!$F$6:$F$115=$C 6)*('T&A'!$AW$6:$AW$115=D$5)*(T&A'!$K$6:$K$115))))) You can copy the formula into E6 if that is under "SHIP", and then you can put a simple sum formula in F6 for the total. Hope this helps. Pete > Hi Pete_Uk > Thanks for your reply once again. I can write formula which i want. i can [quoted text clipped - 160 lines] > > - Show quoted text - Yes, u are right. I can asked u, if I can use one more condition in Col A6 (month name like Jan, Feb..) and data in Col M (Date) in T&A means month wise total. all are same. only add one more condition. then what change in formula. please tell. Thanks RKS > OK, I think you have "AIR" in D5 of a different sheet, and your pull- > downs for PM and Buyer are in B6 and C6 of that sheet. So, try this [quoted text clipped - 178 lines] > > > > - Show quoted text - You need to add this condition to each SP term: (TEXT('T&A'!$M$6:$M$115,"mmm")=$A6)* so your formula will become: =IF(AND($B6="",$C6=""),SUMPRODUCT((TEXT('T&A'!$M$6:$M$115,"mmm")= $A6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A'!$K$6:$K $115)),IF($B6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$115,"mmm")=$A6)*('T&A'! $F$6:$F$115=$C6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A'!$K$6:$K $115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$115,"mmm")=$A6)*('T&A'! $E$6:$E$115=$B6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A'!$K$6:$K $115)),SUMPRODUCT((TEXT('T&A'!$M$6:$M$115,"mmm")=$A6)*('T&A'!$E$6:$E $115=$B6)*('T&A'!$F$6:$F$115=$C6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A'!$K $6:$K$115))))) Hope this helps. Pete > Yes, u are right. I can asked u, if I can use one more condition in Col A6 > (month name like Jan, Feb..) and data in Col M (Date) in T&A means month wise [quoted text clipped - 187 lines] > > - Show quoted text - its not working. answer are wrong. u missed some condition. Condition 1. A6,B6 blank then calculate sum of qty C6 wise 2. B6,C6 blank then calculate sum of qty A6 wise 3. A6,C6 blank then calculate sum of qty B6 wise Any one condition, any two condition and all condition means check all possiblities A6,B6 and C6. Thanks RKS > You need to add this condition to each SP term: > [quoted text clipped - 207 lines] > > > > - Show quoted text - I think that will make your formula too long - I assumed that A6 would not be blank, but if it can be then you have these conditions: A6,B6,C6 blank A6, B6 blank A6,C6 blank B6,C6 blank A6 blank B6 blank C6 blank none of these blank whereas before we only had these conditions: B6,C6 blank B6 blank C6 blank none of these blank Thus you would need seven IFs and for each IF you would have a SUMPRODUCT term to cover the variables that were not blank - I think it has become too complex, so you might have to think of another way of doing it. Hope this helps. Pete > its not working. answer are wrong. u missed some condition. > [quoted text clipped - 273 lines] >> > >> > - Show quoted text - Not sure if you are still monitoring this thread, but here's a way of doing it which accounts for all 8 conditions of the three cells A6, B6 and C6 being blank or not. First of all, you need to add this user-defined function to your workbook: Function eval(func As String) Application.Volatile eval = Evaluate(func) End Function Then in cell D6 you can add this formula: =eval("=SUMPRODUCT("&IF($A6="","","(TEXT('T&A'!$M$6:$M$115,"&CHAR(34)&"mmm"&CHAR(34)&")=$A6)*")&IF($B6="","","('T&A'!$E$6:$E$115=$B6)*")&IF($C6="","","('T&A'!$F$6:$F$115=$C6)*")&"('T&A'!$AW$6:$AW$115="&CHAR(34)&"AIR"&CHAR(34)&")*('T&A'!$K$6:$K$115))") The formula for E6 is very similar (just changed AIR to SHIP): =eval("=SUMPRODUCT("&IF($A6="","","(TEXT('T&A'!$M$6:$M$115,"&CHAR(34)&"mmm"&CHAR(34)&")=$A6)*")&IF($B6="","","('T&A'!$E$6:$E$115=$B6)*")&IF($C6="","","('T&A'!$F$6:$F$115=$C6)*")&"('T&A'!$AW$6:$AW$115="&CHAR(34)&"SHIP"&CHAR(34)&")*('T&A'!$K$6:$K$115))") As these are long formulae be wary of spurious line-breaks if you copy/paste them from the newsgroups - you often get a hyphen character as well. These will now cope with cells A6, B6 and C6 of your summary sheet being empty, or you can put values in them. The formulae are not designed to be copied down, though if you want to (e.g. to see all months summarised) you will need to change the references to A6, B6 and C6 to suit the row the formula is on, as these are contained within strings. Don't forget that your month in A6 should be the first 3 characters only. I have tested this out on a mock-up of your file, and it works for me - let me know how you get on. Hope this helps. Pete >I think that will make your formula too long - I assumed that A6 would not >be blank, but if it can be then you have these conditions: [quoted text clipped - 302 lines] >>> > >>> > - Show quoted text - Thanks Pete_Uk. You are right its so complex. but I need, so now I left the PM condition. and use Month condition. so now we have 2 condition. Now i wants another help you. I am useing in A1 in Month validation List (Jan, Feb...) and in B1 year validation list i wants when i select month in A1 thru validation list then we access the B1 validation cell for year if we not select month in a1 then not using b1 year validation. if it possible. pls tell me. my purpose is in data sheet we have shipdt column. I convert into monthyear (like 022008 - feb 2008) and my summary report combine (convert month name into number+year). please tell me how can it. main purpose I wants total qty. criteria ((month+year), buyer). RKS > I think that will make your formula too long - I assumed that A6 would not > be blank, but if it can be then you have these conditions: [quoted text clipped - 280 lines] > >> > > > > > > > > > > > sum of quantity PM, > >> > > > > > > > > > > > buyer and Mode wise You've responded to an earlier post, so I'm not sure if you have seen my post from last night where I showed that you could cope with all 8 criteria by means of the eval UDF. Now you seem to be asking for a function to convert "Jan" and a year into a format to match your 012008 in the other sheet. Would it be possible to put this in C1, so the other formulae can refer to it? Do you not have any actual dates in your data? Please post a copy of the formula you are using now, so I can show the necessary amendments to that. Pete > Thanks Pete_Uk. > You are right its so complex. but I need, so now I left the PM condition. [quoted text clipped - 36 lines] > > > Pete Did you NOT see my solution? It's simple and it works and it can be easily modified. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("a1:c1")) Is Nothing Then lr = Cells(Rows.Count, "d").End(xlUp).Row If Application.CountA(Range("a1:c1")) = 0 Then Range("d1").Formula = "=sum(d3:d" & lr & ")" Else If Range("a1") <> "" Then a = "(a3:a" & lr & "=a1)*" If Range("b1") <> "" Then b = "(b3:b" & lr & "=b1)*" If Range("c1") <> "" Then c = "(c3:c" & lr & "=c1)*" d = "d3:d" & lr & ")" Range("D1").Formula = "=sumproduct(" & a & b & c & d End If Range("E1") = "'" & Range("D1").Formula End If End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks Pete_uk for ur reply. Its working but result are wrong. please see > my [quoted text clipped - 74 lines] >> > Thanks in advance >> > RKS Just a word to the wise. Many of us will ignore "urgent" requests. All requests get the same priority around here. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi all > [quoted text clipped - 31 lines] > Thanks in advance > RKS Thanks. Keep in Mind > Just a word to the wise. Many of us will ignore "urgent" requests. All > requests get the same priority around here. [quoted text clipped - 34 lines] > > Thanks in advance > > RKS pm buyer mode qty AA XX AIR 100 BB YY AIR 100 AA YY AIR 100 CC XX JOE 100 AA XX SHIP 100 Assumes col A-D above. Could even be assigned to a worksheet_change event Sub makesumproductformula() x = Application.CountA(Range("a1:d1")) 'MsgBox x If x = 0 Then Range("e1").Formula = "=sum(d3:d33)" Else If Range("a1") <> "" Then a = "(a3:a33=a1)*" If Range("b1") <> "" Then b = "(b3:b33=b1)*" If Range("c1") <> "" Then c = "(c3:c33=c1)*" d = "d3:d33)" Range("e1").Formula = "=sumproduct(" & a & b & c & d End If End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi all > [quoted text clipped - 31 lines] > Thanks in advance > RKS Even better. Right click sheet tab>view code insert this> use DELETE key to change a1:c1 to BLANK. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("a1:c1")) Is Nothing Then x = Application.CountA(Range("a1:d1")) 'MsgBox x If x = 0 Then Range("e1").Formula = "=sum(d3:d33)" Else If Range("a1") <> "" Then a = "(a3:a33=a1)*" If Range("b1") <> "" Then b = "(b3:b33=b1)*" If Range("c1") <> "" Then c = "(c3:c33=c1)*" d = "d3:d33)" Range("e1").Formula = "=sumproduct(" & a & b & c & d End If End If End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > > pm buyer mode qty [quoted text clipped - 61 lines] >> Thanks in advance >> RKS Small refinement Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("a1:c1")) Is Nothing Then lr = Cells(Rows.Count, "d").End(xlUp).Row If Application.CountA(Range("a1:c1")) = 0 Then Range("d1").Formula = "=sum(d3:d" & lr & ")" Else If Range("a1") <> "" Then a = "(a3:a" & lr & "=a1)*" If Range("b1") <> "" Then b = "(b3:b" & lr & "=b1)*" If Range("c1") <> "" Then c = "(c3:c" & lr & "=c1)*" d = "d3:d" & lr & ")" Range("D1").Formula = "=sumproduct(" & a & b & c & d End If Range("E1") = "'" & Range("D1").Formula End If End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Even better. Right click sheet tab>view code insert this> use DELETE key > to change a1:c1 to BLANK. [quoted text clipped - 80 lines] >>> Thanks in advance >>> RKS |
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.