SUMIFS Function returning 0 with only minor change

The formula that I am using is trying to pull the percentage of work done on a specific project within a certain week. I am trying to capture a "This Week" and "Last Week"

The formula for "This Week" IS WORKING and it is as follows:

=SUMIFS({Tracker Range 5}, {Tracker Range 4}, WEEKNUMBER(TODAY()), {Tracker Range 6}, [Primary Column]@row)

Where "Tracker Range 5" is the column of percentages, "Tracker Range 4" is the column of week numbers, and "Tracker Range 3" is the column of project names (the primary column on my metrics sheet is the project names, hence the[Primary Column]@row)

The formula for "Last Week" IS NOT WORKING and it is as follows:

=SUMIFS({Tracker Range 5}, {Tracker Range 4}, WEEKNUMBER(TODAY()-1), {Tracker Range 6}, [Primary Column]@row)

The only difference in this formula is the "-1" within the week number formula. Before it is mentioned, the formula =WEEKNUMBER(TODAY()-1) is working in a separate cell and pulls up the number that I am looking for (so does =WEEKNUMBER(TODAY())-1 and =WEEKNUMBER(TODAY(-7)) ).

Each way I have thought to configure this formula has not worked and logically it appears to make sense. Please let me know what you all think, willing to try anything. I have had a hard time looking for a similar formula that can be used in the same way with cycling data.

Find more posts tagged with

Accepted answers

All comments

Brian Wilson DC

I was able to get your formula to work. You have the correct formula in the "Not Working Section" except you need to change the WEEKNUMBER(TODAY()-1) to WEEKNUMBER(TODAY()-7)

I don't see where you've listed that as an example. By using the "Today" function and modifying it by -1 in your example it is taking today's date and subtracting just 1 single day, making your information appear in the same week.

In the image below I typed in the following formulas to return the data. Please note that we are in the third calendar week of the year right now, which is why I am using weeks 1, 2 and 3:

For "Work Complete This Week" on row 1 the formula I have is:

=SUMIFS([% Complete]:[% Complete], [Week Number]:[Week Number], WEEKNUMBER(TODAY() - 14), [Project Names]:[Project Names], [Project Names]@row)

For "Work Complete This Week" on row 2 the formula I have is:

=SUMIFS([% Complete]:[% Complete], [Week Number]:[Week Number], WEEKNUMBER(TODAY() - 7), [Project Names]:[Project Names], [Project Names]@row)

For "Work Complete This Week" on row 3 the formula I have is:

=SUMIFS([% Complete]:[% Complete], [Week Number]:[Week Number], WEEKNUMBER(TODAY()), [Project Names]:[Project Names], [Project Names]@row)

And to show you that the SUMIFS portion is working I added a few more from week "2" at 5% each to give you an additional 40%, bringing that week's total completion for that project to 60%:

I hope this helps!

NicoLHC

Hello @corinne.swampfox it would be easier if you can share the sheet i have a formula in mind , nico.roepnack@lighthouseconsultings.com further did you try to use the AI already? Feel free to visit our next Smartsheet Prompt Engineering Workshop.

=SUMIFS( {Tracker Range 5}, {Tracker Range 4}, IF(WEEKNUMBER(TODAY()) = 1, 52, WEEKNUMBER(TODAY()) - 1), {Tracker Range 6}, [Primary Column]@row

Brian Wilson DC

Also, to further that, if you wanted to return your values for this week and last week in the same cell you could use this modified SUMIFS formula with the OR statement:

=SUMIFS([% Complete]:[% Complete], [Week Number]:[Week Number], OR(@cell = WEEKNUMBER (TODAY()), @cell = WEEKNUMBER(TODAY() - 7)), [Project Names]:[Project Names], [Project Names]@row)

So in our example above this would return the value of 100%, which is all of this week (week 3) values at 40% and all of last week (week 2's) values of 60%.

corinne.swampfox

I have tried the solutions presented above and they still are not working. Adding the "-7" outside of the TODAY() still returned a solution of 0. It returned a 0 even when I manually put in a "2" for the formula instead of using WEEKNUMBER.

Still looking for solutions… :(

Brian Wilson DC

@corinne.swampfox It appears that you are importing the data from another sheet to create this formula. Just to confirm, are you an owner or admin level status on both the source sheet(s) and destination sheet?

Brian Wilson DC

@corinne.swampfox Another "crazy" suggestion, but I have noticed this to be a problem sometimes… when you setup the "last week" formula, did you retype the entire formula or just cut and paste it into the new cell and then add your modifier of -1, or -7? I have found that sometimes if I copy and paste a base formula for some reason it doesn't work, even if all logic says that it should.

If that is the case that you copied it, try retyping the formula we both know should work (from above) and see what happens…. just a thought!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.