# Sumproduct with Date Calculation



## KL28 (Dec 19, 2022)

I am trying (without success) to use either Countifs or Sumproduct to count how many cancellations were made with less than 48 hours notice.

So, here *Status = Cancelled* and *Start Date >= 01/11/2022* and *Start Date <= 30/11/22* AND where the difference in start date and start time and cancellation date and cancellation time is less than 48 hours.

In the example below, I would expect the result to be 5.



Monthly_Dashboard - Peelhouse.xlsmABCDEFGHI1Start DateStart TimeIDStatusCancelled DateCancelled TimeCriteria213/10/202209:00:002355Cancelled12/10/202209:47:3401/11/2022318/10/202209:00:001799Completed30/11/2022421/10/202209:55:007791CompletedCancelled501/11/202210:00:007445Did not attend601/11/202210:00:00147Cancelled21/07/202211:06:29701/11/202210:00:00490Cancelled23/10/202214:49:00801/11/202210:25:00537Cancelled29/10/202213:03:19902/11/202210:30:007445Did not attend1003/11/202210:30:006253Completed1104/11/202210:30:007737Cancelled04/11/202209:23:231205/11/202211:00:002854Cancelled05/11/202209:41:191306/11/202212:15:00510Completed1406/11/202212:30:007441Cancelled10/10/202216:10:491507/11/202212:40:00510Completed1609/11/202212:40:005166Cancelled09/11/202209:39:031711/11/202214:00:003166Completed1812/11/202214:30:005314Cancelled05/09/202216:01:31Sheet2


----------



## JamesCanale (Dec 19, 2022)

I'm getting three.  Your first two red cancelled - one is in Oct, the other is more than 2 days early.

```
=SUMPRODUCT(--(D2:D18="Cancelled"),--((A2:A18+B2:B18)<(E2:E18+F2:F18+2)),--(A2:A18>=I2),--(A2:A18<=I3))
```


----------



## KL28 (Dec 19, 2022)

JamesCanale said:


> =SUMPRODUCT(--(D2:D18="Cancelled"),--((A2:A18+B2:B18)<(E2:E18+F2:F18+2)),--(A2:A18>=I2),--(A2:A18<=I3))



Great, thank you so much.  Apolgies, I meant I was expecting 3...got carried away!


----------

