Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

Afternoon everyone,

Y'all have been a great deal of help to me and the ongoing project I have been working on so I appreciate any time you have to review my inquiry... I have created a template where I am keeping track of incoming volume, volume in queue, volume completed, volume incomplete, and volume rolled over (second tab of attached workbook).

I am not trying to change the format of the template (i.e. it needs to remain horizontal). What I am trying to do is capture data that is outside of my expectation of completed work, which is 1, 7 and 30 business days.

My current formula (WOH!column J) calculates any value in the INCOMP columns of the Completed Work tab if that INCOMP value is present on any day prior TODAY(). When I replace the &TODAY()-1 with &TODAY()-7 or &TODAY()-30, it still works as I expect it to.

Please Login or Register  to view this content.

When I reformatted my template to account for rollover work, I was provided the following formula to capture any work that was incomplete and rolled over to the next business day when the Rollover Section for that day is marked as "No" as well.

Please Login or Register  to view this content.

This works beautifully, except for the fact that I need to be able to use the formula based on a date because I have different expectations. For this example, I am using 1 business day, when I need to format the formula so that it could also take into account rollover work that have not been completed greater than 7 or 30 days ago.

I have tried adjusting my current formula to be written similarly to the one provided, but I get an error. What am I doing wrong?

Please Login or Register  to view this content.

WORK.xlsx

I had a minor typo when testing the formula, thank you for pointing it out! I appreciate your time.