how to seperate 650 ml sold qty and 330 ml sold qty with formula

[rahulshewale1]

i have attached sheet .i want to seperate 650 ml sold qty and 330 ml sold qty with formula.

 

[NARAYANK991]

Hi ,

For the first , you can use the following formula :

=SUMIF($A$2:$A$28,"*" & "650 ML" & "*",$B$2:$B$28)

The correct answer is 324 , since 279 does not include the quantity in row 21.

For the second , the situation is complicated by the need to recognize the 3 and the 5 in the description involving pints and buckets.

Narayan

 

[rahulshewale1]

ok thanks for help

 

[NARAYANK991]

Hi ,

For the second , try this :

=SUMIF($A$2:$A$28,"*" & "330 ML" & "*",$B$2:$B$28) + SUM(IF(ISNUMBER(SEARCH("PINTS",$A$2:$A$28)),$B$2:$B$28) * IF(ISNUMBER(SEARCH(" 3 ",$A$2:$A$28)),3,IF(ISNUMBER(SEARCH(" 5 ",$A$2:$A$28)),5,0)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[rahulshewale1]

wow great .....it works supurb........thanks again

 

[John Jairo V]

Hi to all!

Another way:

for first point:
=SUMIF(A2:A28,"*650 ML*",B2:B28)

for second point(No CSE):
=SUM(SUMIF(A2:A28,{"*330 ML*";"* 3 * PINTS";"* 5 * PINTS"},B2:B28)*{1;3;5})

Blessings!

 

[rahulshewale1]

Thanks john ....