SUMIFS #INCORRECT ARGUMENT SET

Can some please explain the reason for the error? I don't understand what is wrong with what I have typed.

=SUMIFS({STD HOURS}, {STATUS}, ="*** COMPLETE ***", {COMPLETION DATE}, AND(IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row, IFERROR(YEAR(@cell), 0) = {YEAR SELECTED}))

{STD HOURS} is a column with a formula that returns a number but sometimes returns a blank cell.

{STATUS} is simply a dropdown text column.

{COMPLETION DATE} is a column with only dates or blank cells.

{YEAR SELECTED} is a single cell reference containing a number.

[Week #]@row is a primary column containing a number.

Find more posts tagged with

Formulas

Accepted answers

All comments

David Fiorino

I am not sure what you are trying to exclude using the week portion of this in the aggregation without a look at a sample sheet. It looks like you want to sum standard hours if it has a 'complete' status and you want to be able to choose which year to look at. You can use a formula to set the status cell to read 'complete' when a date exists in the completion date column =IF(ISDATE([Completion Date]@row), "Complete", "In Progress"). Then, use a helper column to parse the year =IFERROR(YEAR([Completion Date]@row), ""). Pick a year and it will sum all completed projects with the formula =SUMIFS([STD HRS]:[STD HRS], Status:Status, "Complete", Year:Year, [YEAR SELECT]#)

This may be oversimplified but, I am not sure on the use case without more detail on how the week number plays into this sheet.

Preston Murphy

@David Fiorino Thanks for your response. The Week # is the only value that is actually within the sheet I am writing the formula in. This sheet has a list of week numbers, 1 through 53, and references another "database" sheet in order to see which orders have been completed in that week. In essence, this sheet is creating weekly summary data of the "database" sheet. Additionally, there is a third sheet which simply contains a single number, which is the year for which the data is pulled. So if the user enters 2021 on that sheet, the weekly summary sheet will make sure to take only data from 2021, since there could be more than one year of data on the sheet.

So, in the formula I wrote, I was simply trying to check which orders in the database sheet were completed during that week and were also in the same year specified in the third sheet.

Please let me know if this makes more sense. Thank you.

Below, first, weekly summary sheet, second, sheet that contains the desired year, third, database sheet

David Fiorino

Now I see. There are a couple of ways to show it. You can reference the third sheet for the year input instead of using the sheet summary field as I did below. Replace YEAR # at the end of the formula shown with the sheet and cell reference you want to use. I chose this way to keep me from creating another sheet and having the user go between sheets to enter and see the results. Create two helper columns in your source DB and put the weeknumber formula in one and the year formula in the other. It looks like you have several sheets instead of housing it one DB so this again may be oversimplified. You can do the datepart within a lookup formula but this way makes it easier to reuse this info for other summary visuals like the report below this.

=SUMIFS({New Sheet2 Range 1}, {New Sheet2 Range 2}, "Complete", {New Sheet2 Range 3}, Week@row, {New Sheet2 Range 4}, Year#)

Another option is to create a summary report from the source using the helper columns I added and instead of inputting the year into a field, the user expands the grouped year they want to view. It totals things up so you do not need the formula or second sheet. This only shows weeks with data which will save the user from hunting through rows with zeroes to find weeks with data. The key to separating the week numbers that repeat each year is having the year helper column.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.