Why does this say invalid operation

Alex191 · 05/03/23

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

Austin Smith · 05/03/23

@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 · 05/03/23

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 · 05/09/23

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

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.

Why does this say invalid operation

Answers

Help Article Resources

Categories