how to filter or vlookup
Hi All,
I have a spreadsheet which contains lots of items but i want Category 1 items.
what want to do is:
In an order, if Product is more then £150 its a boiler and if it's less then £150 is an accessory.
I want to see how many orders are there in total which have Boilers and out of these orders how many have Boilers +accessories.
Can someone help please.
Regards,
A
Dreport--2-.xlsx
I have a spreadsheet which contains lots of items but i want Category 1 items.
what want to do is:
In an order, if Product is more then £150 its a boiler and if it's less then £150 is an accessory.
I want to see how many orders are there in total which have Boilers and out of these orders how many have Boilers +accessories.
Can someone help please.
Regards,
A
Dreport--2-.xlsx
ASKER
Thank you Fabrice, but i have category filtered already.
all i want now is If order number has 1 product compare the value if its less then 150£ its accessories so separate that.
else if ordernumber has more then 1 product compare price and if its more then 150£ its a boiler and other product if less then 150 is accessories so this will go in bolier + accessories pot.
all i want now is If order number has 1 product compare the value if its less then 150£ its accessories so separate that.
else if ordernumber has more then 1 product compare price and if its more then 150£ its a boiler and other product if less then 150 is accessories so this will go in bolier + accessories pot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you Eijgil
was this formula was run on CAT 1 all orders?
was this formula was run on CAT 1 all orders?
I used the sheet esprit_sale where the sell price is, because sales amount on sales is a sum of the prices, so comparing price to 150 could be meaningless.
So it is all orders on sheet esprit_sale.
A similar count on sales, accepting the price problem.
Sorted on OrderNumber and Cat.
Many orders have more than one category, so to only look at Cat 1, and ignore others, the formula is
There are 14064 order lines with Cat 1, but only 6614 orders with Cat 1.
B 1572
B+A 2964
A 2078
So it is all orders on sheet esprit_sale.
A similar count on sales, accepting the price problem.
Sorted on OrderNumber and Cat.
Many orders have more than one category, so to only look at Cat 1, and ignore others, the formula is
=IF(N2=1,IF(OR(A2<>A1,N1="0"),IF(COUNTIFS(A2:A100,A2,N2:N100,1)=1,IF(B2>=150,"B","A"),IF(COUNTIFS(A2:A100,A2,N2:N100,1,B2:B100,">=150"),"B+A","A")),""),"")There are 14064 order lines with Cat 1, but only 6614 orders with Cat 1.
B 1572
B+A 2964
A 2078
ASKER
excellent
PS: I'm not sure the identifying product category based on the price is a wise idea.