Formula for SUMIF all orders within last 90 days

Laurie Mason

I have an order sheet where individuals can order items, but we have a restriction that they can only order 5 "coupons" in a 90 day period. I need a "Block Order" box to be checked on the row if

1. The order is > 5 "coupons" OR
2. If the individual ordering has ordered > 5 "coupons" over the last 90 days (may be in multiple orders).

Here is the formula I have so far:

= OR([# of items]@row > 5, SUMIFS([# of items]:[# of items], Name:Name, Name@row , [Coupon Date]:[Coupon Date], ">=" + ([Coupon Date]@row - 90), [Coupon Date]:[Coupon Date], "<=" + [Coupon Date]@row ) >= 5)

The first part works wonderfully. The second part is what I'm having trouble with. I've tried several variation and it just doesn't seem to be able to pick up when other rows have orders within the last 90 days and the sum of the items is > 5.

Here is a sample of my data exported to Excel to facilitate removing personal data:

The yellow rows should have TRUE in the BLOCK ORDER column. Any thoughts? I'm sure it's something simple I'm missing.

Naeem Ejaz

=OR([# of items]@row  > 5,SUMIFS([# of items]:[# of items],Name:Name,Name@row ,[Coupon Date]:[Coupon Date],>=([Coupon Date]@row -90),[Coupon Date]:[Coupon Date],<=[Coupon Date]@row )> 5)