Countif completed last week

Options

I am trying to write a formula that shows volume of work completed last week. I am getting a result, however it is not grabbing the full list of data. I have played around with different day ranges but it doesn't seem to change my result. The formula I have references other sheets. Please see attached images. I have added a filter to the source sheet to show what should be captured.

=COUNTIFS({Completed Files Focus Tracker Range 1}, [Assigned to]@row, {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, =WEEKNUMBER(TODAY(-1)))



Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Spencor_Luze

    Did you try updating the TODAY() function as @Amanda Alv suggested?

    TODAY(-1) says "yesterday"

    it sounds like you may be looking for last week, which would be subtracting 1 from Today's Weeknumber (instead of directly from today).

    WEEKNUMBER(TODAY()) -1 < after closing ))

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Ah! That's super helpful, thank you!

    So INVALID REF means that there's something going on with {these references}

    Can you first check each one of these to make sure it's looking at the correct column:

    • {Completed Files Focus Tracker Range 1}
    • {Completed Files Focus Tracker Range 2}
    • {Completed Files Completed week}

Answers

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    Options

    Hi,

    Two things to look into. On your reference sheets, are you selecting the column headers, to ensure any newly added data gets included in your formulas?

    Second, I believe your "Weeknumber" formula needs to be updated to =WEEKNUMBER(TODAY()) -1) in order for it to take a full week from today. So this assumes it is always run the Monday-Sunday after the actual week of data you are calculating.


    Let me know if you have any questions, hope that helps! If that doesn't do it we might need to dig in more to the final part of the full formula. On it's own, the Weeknumber portion I provided would work.

  • Spencor_Luze
    Options

    Hello,

    Yes, my references are based on the column header to capture any new data. These numbers have updated as volumes rise, but they are still not collecting complete data. For instance I am getting a combined 10 for user Krista, but she is actually at 16.

  • Genevieve P.
    Options

    Hi @Spencor_Luze

    Is your Assigned To column in the source sheet a Multi Select column?

    If so, you'll want to use the HAS function to see if a cell has the Assigned To value, even if it's selected with others.

    Try:

    =COUNTIFS({Completed Files Focus Tracker Range 1}, HAS(@cell, [Assigned to]@row), {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, =WEEKNUMBER(TODAY(-1)))


    Cheers,

    Genevieve

  • Spencor_Luze
    Options

    @Genevieve P. They are single select only columns. I did try adding the HAS function incase something was changed to allow for multi select, but my values did not change. I feel this should be a very easy formula so I am not sure why it isn't working correctly.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Spencor_Luze

    Did you try updating the TODAY() function as @Amanda Alv suggested?

    TODAY(-1) says "yesterday"

    it sounds like you may be looking for last week, which would be subtracting 1 from Today's Weeknumber (instead of directly from today).

    WEEKNUMBER(TODAY()) -1 < after closing ))

  • Spencor_Luze
    Options

    @Genevieve P. Actually, while the formula seemed to be working well yesterday, it has now become inaccurate again. Second, if I move the -1 completely out of the (Today()) as shown, the formula comes back as invalid.

  • Genevieve P.
    Genevieve P. Employee
    edited 04/27/22
    Options

    Hi @Spencor_Luze

    My apologies, I just realized we don't have anything to compare the value to - you'll need to look for if the weeknumber of the cell in that column = the weeknumber of last week:

    WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1


    =COUNTIFS({Completed Files Focus Tracker Range 1}, [Assigned to]@row, {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1)

    Cheers,

    Genevieve

  • Spencor_Luze
    Options

    @Genevieve P. that is coming back as unparseable I tried playing with it to look at different references from the source sheet and could not get those to work either

  • Genevieve P.
    Options

    Hi @Spencor_Luze

    Did you re-create it in your sheet or Copy/Paste? Can you post a screen capture of your sheet with the formula open?

  • Spencor_Luze
    Options

    Hey @Genevieve P.

    I tried both copy and paste and writing it out


  • Genevieve P.
    Options

    Hi @Spencor_Luze

    It looks like you may just have an extra closing parentheses at the end! Try removing it out so you're ending with one )

  • Spencor_Luze
    Options

    @Genevieve P. sorry, i did try doing that yesterday as well. It comes back as an invalid reference


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Ah! That's super helpful, thank you!

    So INVALID REF means that there's something going on with {these references}

    Can you first check each one of these to make sure it's looking at the correct column:

    • {Completed Files Focus Tracker Range 1}
    • {Completed Files Focus Tracker Range 2}
    • {Completed Files Completed week}
  • Spencor_Luze
    Options

    @Genevieve P. ooffda that unearthed the problem(s) caused by me just not thinking clearly. One of the references was not actually a reference at all but typed text, the weeknumber reference was pointing to my completed week number helper column instead of the date completed column so it was not returning a result. Thank you :)

  • Genevieve P.
    Options

    Aha! Well-found! I'm glad we got there in the end. 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!