Countif completed last week

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 ))

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}

All comments

Amanda Alv

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.

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.

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

@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.

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. 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.

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

@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

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

Hey @Genevieve P.

I tried both copy and paste and writing it out

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

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

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}

@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 :)