SOLVED

Guidance on a possible formula

New Contributor

Hello all,

 

I am fairly new to excel and tried a lot of searches in google but was not successful. Always found a way to do half of what I needed but never the whole thing. Maybe one of you can help me here. I created a file as an example. I want to identify the value of all the orders where the product "Pizza*" was bought together with the product "Spice 2" and has an expiration for Pizza of 30 days. I need the full order value (Pizza* + spice x + spice x etc).

 

This feels so easy but I am stumped. 

 

Any help is appreciated. 

pizza shop.PNG

2 Replies
best response confirmed by PeanutButterExcelTime (New Contributor)
Solution

@PeanutButterExcelTime 

I'd add a helper column to the table. Name it Include and enter the formula

 

=COUNTIFS([Order ID],[@[Order ID]],[Product Name],"Pizza*",[Expires in days],30)*COUNTIFS([Order ID],[@[Order ID]],[Product Name],"Spice 2")

 

in row 2. This should propagate to the other rows automatically. The formula will return 1 for orders that meet the criteria, and 0 for the rest.

The fill order value of the orders meeting the criteria is

 

=SUMIFS(Orders[Price],Orders[Include],1)

 

where Orders is the name of the table.

See the attached sample workbook.

@Hans Vogelaar thank you for the reply! Your answer really helped!

 

Have a great new year!