Count completed dates once from two columns counting only the most recent competion date by week


I'm trying to count the number of quotes completed by week. The challenge is there are two departments who may work on a quote and each enter their own completion date. Sometimes only one department works on a quote so only one of the two completion date columns is filled in. In a nutshell I want to only count "Type A" quotes that were completed within a specific weekending date range (ex. week that ended on 8/8/20) based upon the most recent completion date of department "A" or "B" for the quote. Keep in mind that either department "A" or "B's" completion date field may be blank if they didn't need to work on it.

I figured out how to count "Type A" quotes based on one department's completion date but counting both date ranges and selecting the most recent has me stumped. Below is the formula I've came up with so far:

=COUNTIFS({Pre-Sales Request Range 1}, ="NO DWG, EWR ONLY", {Pre-Sales Request Range 2}, AND(@cell >= ([Week Ending Date]21 - 6), @cell < ([Week Ending Date]21 + 1)))

Any help would be greatly appreciated, Thanks!

Best Answer


  • Lynn Strahm

    Thanks for the prompt response! I'm confused on what range the "created:created" should tie back to in the formula provided. Is this the week number that we want to count completed dates for?

  • Lynn Strahm

    Some additional information, here is the formula I'm currently using: =COUNT(COLLECT(WeekNumber21, {Pre-Sales Request Range 2}, WEEKNUMBER(@cell) = WEEKNUMBER(MAX(MAX({Pre-Sales Request Range 2}), MAX({Pre-Sales Request Range 3}))), {Pre-Sales Request Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(MAX(MAX({Pre-Sales Request Range 2}), MAX({Pre-Sales Request Range 3}))))). It doesn't seem to matter what range or cell I use for the COLLECT function rage I get a "1" returned for this formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!