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 -