| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition - urgent
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 On Mar 25, 1:00*pm, RKS wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition - urgent
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 "Pete_UK" wrote: 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 On Mar 25, 1:00 pm, RKS wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition - urgent
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 On Mar 26, 5:34*am, RKS wrote: 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 "Pete_UK" wrote: 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 On Mar 25, 1:00 pm, RKS wrote: 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- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
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 "Pete_UK" wrote: 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 On Mar 26, 5:34 am, RKS wrote: 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 "Pete_UK" wrote: 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 On Mar 25, 1:00 pm, RKS wrote: 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- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
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 On Mar 26, 12:40*pm, RKS wrote: 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 "Pete_UK" wrote: 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 On Mar 26, 5:34 am, RKS wrote: 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 "Pete_UK" wrote: 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 On Mar 25, 1:00 pm, RKS wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition - urgent
Just a word to the wise. Many of us will ignore "urgent" requests. All
requests get the same priority around here. -- Don Guillett Microsoft MVP Excel SalesAid Software "RKS" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
Thanks. Keep in Mind
"Don Guillett" wrote: Just a word to the wise. Many of us will ignore "urgent" requests. All requests get the same priority around here. -- Don Guillett Microsoft MVP Excel SalesAid Software "RKS" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
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 "RKS" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
Even better. Right click sheet tabview 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 "RKS" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif with 3 condition
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Even better. Right click sheet tabview 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 "RKS" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SumIF for more than one condition | Excel Worksheet Functions | |||
| Urgent SUMIF problem | Excel Worksheet Functions | |||
| basic sumif function help (urgent) | Excel Worksheet Functions | |||
| sumif on more than one condition | Links and Linking in Excel | |||
| sumif help ---urgent | Excel Worksheet Functions | |||
Hybrid Mode
