I am trying to count data from multiple sheets based on two criteria. The sheets are all formatted the same and represent shipping schedules. The first is a text match to a specific customer name and the second is to count based on a numeric value which can be -1,0,1,2,3+ representing order lead time in days. The lead time is calculated for each row on each worksheet per NETWORKDAYS function based on order date versus ship date.
Sheets 1-4 are the schedules and sheet 5 is a summary sheet to measure the order lead time per week and per customer. On summary sheet, column A has customer names, column B is total orders for that customer from sheets 1-4 (Weeks 1 – 4). Column C needs to display # of 0 days lead time orders, column D needs to display # of 1 day lead time orders, column C needs to display # of 0 days lead time orders, column E needs to display # of 2 days lead time orders, column F needs to display # of 3 days or more lead time orders. On the summary sheet, the upper table displays only the order lead time data in the respective columns in weekly buckets. I need the lower table to display the data per customer in the respective columns (0, 1, 2, 3+ days lead time). I also need it to tally MTD (Month To Date) and to ignore if column F is blank even though -1 shows in column I due to formula. See Week 4 on summary sheet – 12 displayed in column C but should be nothing/blank as there are no orders in Week 4 yet.
I have tried SUMPRODUCT and COUNTIF and even some nesting but I cannot seem to get the syntax or function combinations right. I got it to work on one sheet but not multiple sheets. Thanks in advance for your kind assistance.