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
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!