Hi, in the attached I'm trying to calculate a total no. of boxes for delivery this week in cell F2, based on the following logic:

1. Total no. of boxes = Column B/£1.25 (each box costs £1.25 so a customer spending £2.50 is expecting 2 boxes)
2. Subtract customers who are on vacation this week and therefore who don't need a box - denoted by any character whatsoever in Column C
3. Filter by the delivery week in Column D (i.e. if it's Week 3 in our 4-week delivery cycle, filter to show only "1,3" and "1,2,3,4"; if it's Week 2, show only "2,4" and "1,2,3,4") to show only customers who are due a delivery this week anyway.

I've managed to achieve steps 1 and 2 but I can't find a way to get the total to subtract those boxes that are not due to be delivered this week - i.e. reflect the filter on Column D. So, in the attached, the total in F2 is shown as 4 because it's correctly calculating the number of boxes due, minus the number of customers on vacation who don't need a box; however if I try to filter out customers who are not due a delivery this week using Column D, it doesn't make any difference. If, for example, it's Week 2 of our 4-weekly delivery cycle, I want to filter in Column D to show only entries containing "2" and I want the total boxes in F2 to update accordingly.

Grateful for your help.

Thanks!
Sample customer spreadsheet.xls