Countif completed last week

Spencor_Luze

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

Genevieve P.

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.

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}

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.

Spencor_Luze

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.

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

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

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

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

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

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

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

Hey @Genevieve P.

I tried both copy and paste and writing it out

Genevieve P.

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

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

Genevieve P.

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

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

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