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