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

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

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

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

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

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 -

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 -

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 -

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

"Pete_UK" wrote:

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 -

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

On Mar 27, 4:52*am, RKS wrote:
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



"Pete_UK" wrote:
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 -- Hide quoted text -

- 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)),SUMP RODUCT(('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))) )

Thanks

"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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



"Pete_UK" wrote:
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 -- Hide quoted text -

- 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$11 5=$C*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$B*6)*('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

On Mar 27, 12:40*pm, RKS wrote:
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)),SUM PRODUCT(('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) )))

Thanks



"Pete_UK" wrote:
*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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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


"Pete_UK" wrote:

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$11 5=$CÂ*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$BÂ*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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)),SU MPRODUCT(('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$11 5))))

Thanks



"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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$11 5,"mmm")=$A6)*('T&A'!
$F$6:$F$115=$C*6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A' !$K$6:$K
$115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$11 5,"mmm")=$A6)*('T&A'!
$E$6:$E$115=$B*6)*('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=$C*6)*('T&A'!$AW$6:$A W$115=D$5)*('T&A'!$K
$6:$K$115)))))

Hope this helps.

Pete

On Mar 28, 4:21*am, RKS wrote:
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



"Pete_UK" wrote:
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$11 5=$C*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$B*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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)),SU MPRODUCT(('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$11 5))))

Thanks

"Pete_UK" wrote:
*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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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


"Pete_UK" wrote:

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$11 5,"mmm")=$A6)*('T&A'!
$F$6:$F$115=$CÂ*6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A '!$K$6:$K
$115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$11 5,"mmm")=$A6)*('T&A'!
$E$6:$E$115=$BÂ*6)*('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=$CÂ*6)*('T&A'!$AW$6:$ AW$115=D$5)*('T&A'!$K
$6:$K$115)))))

Hope this helps.

Pete

On Mar 28, 4:21 am, RKS wrote:
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



"Pete_UK" wrote:
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$11 5=$CÂ*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$BÂ*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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$1 15=Â*CÂ*6)*('T&A'!$AW$6:$AW$115=$D$5)*(T&A'!$K$6:$ K$115))))

Thanks

"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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

"RKS" wrote in message
...
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


"Pete_UK" wrote:

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$11 5,"mmm")=$A6)*('T&A'!
$F$6:$F$115=$C*6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A' !$K$6:$K
$115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$11 5,"mmm")=$A6)*('T&A'!
$E$6:$E$115=$B*6)*('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=$C*6)*('T&A'!$AW$6:$A W$115=D$5)*('T&A'!$K
$6:$K$115)))))

Hope this helps.

Pete

On Mar 28, 4:21 am, RKS wrote:
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



"Pete_UK" wrote:
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$11 5=$C*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$B*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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)),SU MPRODUCT(('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$11 5))))

Thanks

"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

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

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

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

"Pete_UK" wrote in message
...
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

"RKS" wrote in message
...
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


"Pete_UK" wrote:

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$11 5,"mmm")=$A6)*('T&A'!
$F$6:$F$115=$C*6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A' !$K$6:$K
$115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$11 5,"mmm")=$A6)*('T&A'!
$E$6:$E$115=$B*6)*('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=$C*6)*('T&A'!$AW$6:$A W$115=D$5)*('T&A'!$K
$6:$K$115)))))

Hope this helps.

Pete

On Mar 28, 4:21 am, RKS wrote:
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



"Pete_UK" wrote:
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$11 5=$C*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$B*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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)),SU MPRODUCT(('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$11 5))))

Thanks

"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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


"Pete_UK" wrote:

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

"RKS" wrote in message
...
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


"Pete_UK" wrote:

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$11 5,"mmm")=$A6)*('T&A'!
$F$6:$F$115=$CÂ*6)*('T&A'!$AW$6:$AW$115=D$5)*('T&A '!$K$6:$K
$115)),IF($C6="",SUMPRODUCT((TEXT('T&A'!$M$6:$M$11 5,"mmm")=$A6)*('T&A'!
$E$6:$E$115=$BÂ*6)*('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=$CÂ*6)*('T&A'!$AW$6:$ AW$115=D$5)*('T&A'!$K
$6:$K$115)))))

Hope this helps.

Pete

On Mar 28, 4:21 am, RKS wrote:
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



"Pete_UK" wrote:
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$11 5=$CÂ*6)*('T&A'!$AW
$6:$AW$115=D$5)*(T&A'!$K$6:$K$115)),IF($C6="",SUMP RODUCT(('T&A'!$E$6:$E
$115=$BÂ*6)*('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

On Mar 27, 12:40 pm, RKS wrote:
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$1 15=Â*CÂ*6)*('T&A'!$AW$6:$AW$115=$D$5)*(T&A'!$K$6:$ K$115))))

Thanks

"Pete_UK" wrote:
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

On Mar 27, 4:52 am, RKS wrote:
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

"Pete_UK" wrote:
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

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

On Mar 29, 6:20*am, RKS wrote:
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



"Pete_UK" wrote:
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

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RKS" wrote in message
...
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