Multiple criteria inside CASE WHEN statement
You can use following:


CREATE tABLE test(UserFlag varchar(100),QtyAvailable INT,OrderStatus VARCHAR(100))
GO




SELECT
CASE WHEN
UserFlag IN ( 'On Order', 'Sold Out', 'On Order,Sold Out')
AND 
QtyAvailable = 0
AND 
OrderStatus = 'Pending Fulfillment' 
then 'BackOrder'
ELSE 'Not a BackOrder' 
END AS [Orders Backorder]
,
 CASE WHEN 
      OrderStatus in ('Billed','Partially Fulfilled','Pending Billing','Pending Billing/ Partially Fulfilled')
 THEN 'Shipped' ELSE 'Not Shipped' END AS [Orders Shipped]
FROM test;
GO


Orders Backorder | Orders Shipped
:--------------- | :-------------


db&lt;&gt;fiddle here