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!
Answers
-
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.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!