Why does this say invalid operation

I have a Smartsheet page that I am trying to pull data from between 2 dates. The formula below is the one I am using but it says invalid operation?

=SUMIFS({Work In Progress - 2021 - Range 3}, =[Primary Column]@row, {Work In Progress - 2021 - Job Value Range 3}, {Work In Progress - Job Value Range 1}, <=DATE(2024, 3, 31), {Work In Progress - Job Value Range 1}, >=DATE(2023, 4, 1))

I have tried to change the criterion which then just gives me a £0. I have attached a file so hopefully you can see what I am trying to achieve. As you can see there are different customers and various values which I want to be able to summarise on a separate sheet so I can create a report for each customer spend yearly.

Any help would be greatly appreciated!

Smartsheet Example.docx

Find more posts tagged with

Accepted answers

All comments

Austin Smith

@Alex191

The primary issue is that you have operators out of order (criteria before range). Not knowing what your data sets are, I believe your formula should look like:

=SUMIFS({Work In Progress - 2021 - Range 3}, {Work In Progress - 2021 - Job Value Range 3}, =[Primary Column]@row, {Work In Progress - Job Value Range 1}, <=DATE(2024, 3, 31), {Work In Progress - Job Value Range 1}, >=DATE(2023, 4, 1))

Not sure if that's enough to fix the issue.

Alex191

Hi @Austin Smith ,

I tried the formula in that order but it just gives me a £0 cost. What I need to ideally happen is (match the customer with the primary column - then find the cost for the order on the same line - then find the lines that are within two date ranges).

So at the minute my formula is saying find customer that matches primary column (customer name) then find value then find between the dates. = invalid operation

When I use yours it is reading find customer then find value that matches primary column (customer name) then find between the dates. = £0

What I want to be able to do is a report on the sheet which only shows customers that have spent over £10,000 over the year for example whereas at the minute the only way I can do a report on it is by showing every order and the pie chart takes up a whole screen.

Alex191

https://community.smartsheet.com/discussion/comment/374262#Comment_374262

Hi @Austin Smith ,

So at the minute my formula is saying find customer that matches primary column (customer name) then find value then find between the dates. = invalid operation

When I use yours it is reading find customer then find value that matches primary column (customer name) then find between the dates. = £0

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.