Sumifs with OR condition on 2nd criteria

Question

I am trying to SUMIFs total product quantity based on product's SKU and Status. To keep track of my inventory, I used SUMIFs function to sum the products SKUs and their quantities. But i also wish to only deduct the products based on the status (Ordered/Shipped)

Currently I am using =SUMIFS(SalesTable[Quantity];SalesTable[SKU];[@[Stocks SKU]];SalesTable[Status];"Ordered")

I also tried: SUMIFS(SalesTable[Quantity];SalesTable[SKU];[@[Stocks SKU]];SalesTable[Status];OR("Ordered";"Shipped"))

SUMIFS(SalesTable[Quantity];SalesTable[SKU];[@[StocksSKU]];SalesTable[Status];"Ordered";SalesTable[Status];"Shipped")

How do I add Shipped as the criteria as well? I tried adding it as the 3rd criteria but it doesnt work.

Answer 1

You can use this formula:

=SUMPRODUCT(SalesTable[Quantity]*
        (SalesTable[SKU]=[@[Stocks SKU]]) *
        ((SalesTable[Status]="Ordered") + (SalesTable[Status]="Shipped"))
)

* --> AND-condition

+ --> OR-condition

Answer 2

To create OR criteria with SUMIFS, place the criteria in an array, then wrap the whole thing in the SUM function. For example:

=SUM(SUMIFS(SalesTable[Quantity];SalesTable[SKU];[@[Stocks SKU]];SalesTable[Status];{"Ordered";"Shipped"}))