filter with multiple and or criteria

[raaj11]

Hi i am trying to list all product and product code and quantity where location is "Melbourne" Quantity > 0 and Product_State = New + if product is in melbourne and quantity is 0 but product code exist in column "j" that should also be included in the result. i got the first part working with formula =UNIQUE(FILTER(F:H,(B:B="Melbourne")*(H:H<>"0")*(D:D="NEW"))) but could not figure out the second part to include if product is in melbourne and quantity is 0 but product code exist in column "j" that should also be included in the result.

 

[myall_blues]

Try

=UNIQUE(FILTER(F:H,((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW"))+((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)))))

 

[SunnyAlv]

try

=UNIQUE(FILTER(HSTACK(F:F,IF(H:H=0,J:J,G:G),H:H),(B:B="Melbourne")*(D:D="NEW")))

 

[raaj11]

Try

=UNIQUE(FILTER(F:H,((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW"))+((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)))))

hi Myall_blues
Thanks heaps for the quick reply and the formula worked for the question i asked but looking at the output i realized that i made a mistake in asking the question. lines marked yellow should not have been returned in the result.

only product we need to target from column j are the ones that are not already picked up by the base formula =UNIQUE(FILTER(F:H,(B:B="Melbourne")*(H:H<>"0")*(D:D="NEW")))

 

[myall_blues]

It looks like you missed out the (H:H=0) part in the second part of the formula.

 

[myall_blues]

That should fix it as the other one excludes zeroes, so it can’t be both.

 

[Phuoc]

Maybe like this:

=UNIQUE(FILTER(F:H,(B:B="Melbourne")*(H:H<>"0")*((D:D="NEW")+COUNTIFS(J:J,G:G))))

 

[raaj11]

hi Myall_blues
Thanks heaps for the quick reply and the formula worked for the question i asked but looking at the output i realized that i made a mistake in asking the question. lines marked yellow should not have been returned in the result.

only product we need to target from column j are the ones that are not already picked up by the base formula =UNIQUE(FILTER(F:H,(B:B="Melbourne")*(H:H<>"0")*(D:D="NEW")))

It looks like you missed out the (H:H=0) part in the second part of the formula.

hi Mayall_blues sorry for the late reply. if i keep the H:H=0 part in then it will miss the items that appear on the fixed product list but has 0 quantity in the source array. i think i need to de a better job at defining what i am trying to achieve. expected output is to give me the stock count of each product in melbourne where quantity is > 0 + any product that is on the fixed product (column j ) but has quantity = 0. with this requirement if i keep the (H:H=0) in the or part of your formula that would filter out product that are in column j but in the source array they have quantity 0. i think i might have to work on SunnyAlv approach to first find all product codes where location is melbourne and quantity is > 0 + all product code where quantity = 0 but it exist in fixed product code (column j) then get final output to have this combined product code list vlook or filter against range F:H with only conditions of location = "melbourne" and productstate = "new"

 

[myall_blues]

Did you actually try it before you reached these conclusions?

In your original post you wanted:

where location is "Melbourne" Quantity > 0 and Product_State = New

The first clause in the FILTER formula ((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW")) gives all those results.
You also wanted:

if product is in melbourne and quantity is 0 but product code exist in column "j"

The second clause in the FILTER formula ((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)) gives all those results (noting you asked for quantity is 0).

The '+' sign (shown in red below) between those two clauses means either the results of the first clause OR the results of the second clause can be included in the output (i.e it is a conditional OR, not a conditional AND).
=UNIQUE(FILTER(F:H,((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW"))[B][SIZE=6][COLOR=rgb(184, 49, 47)]+[/COLOR][/SIZE][/B]((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)))))

 

[raaj11]

Did you actually try it before you reached these conclusions?

In your original post you wanted:

The first clause in the FILTER formula ((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW")) gives all those results.
You also wanted:

The second clause in the FILTER formula ((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)) gives all those results (noting you asked for quantity is 0).

The '+' sign (shown in red below) between those two clauses means either the results of the first clause OR the results of the second clause can be included in the output (i.e it is a conditional OR, not a conditional AND).
=UNIQUE(FILTER(F:H,((B:B="Melbourne")*(H:H<>"0")*(D:D="NEW"))[B][SIZE=6][COLOR=rgb(184, 49, 47)]+[/COLOR][/SIZE][/B]((B:B="Melbourne")*(H:H=0)*(COUNTIFS(J:J,G:G)))))

Hi Murray
thanks heaps and sorry for my slow understanding. i have the desired output now.