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

  • Austin Smith
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!