sumif with 3 condition
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 -
|