Why do I have a formula that is not working in sheet summary, yet is working in another sheet.

Options

I have a column with some rows marked as "Active /Won" in the "Project Status" column. I want to sum all of those values from the corresponding column "Project $ Value". I have done this successfully in another sheet, but I cannot get this formula to work on my sheet summary...

In my sheet summary I get "#invalid operation" with the below formula.

=SUMIF([Project Status]:[Project Status], "Active /WON", [Project $ Value]:[Project $ Value])

In my sheet that works I have the below formula.

=SUMIF({Project Status}, "Active /WON", {Project $ Value})

Help Please!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    What happens if you change your Sum column to EAU? To the SUMIFS plural original version (ensure correct syntax), let's add the ISNUMBER criteria.

    =SUMIFS([Project $ Value]:[Project $ Value],[Project $ Value]:[Project $ Value], ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Max Corbin

    To clarify, the sheet you are currently summarizing is the sheet that contains the data you are referencing? Do you have any errors that are present in the Sum column?

  • Max Corbin
    Options

    Yes, that is correct. As far as errors, there are some values that are blank and some that are zero from a formula that are the two columns to the left multiplied together but no values to multiply together entered in those two columns. I am not sure how to tell if they are considered errors.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Just for grins enter your formula as a SUMIFS. It really about starting from scratch.

    Do this by clicking in the cells to bring in the column references and deleting the row number, rather than manually typing the column names. Copy paste the Active/Won into your formula, between the quotes. I'm trying to eliminate any spelling/capitalization issues.

    =SUMIFS([Project $ Value]:[Project $ Value], [Project Status]:[Project Status], "Active /WON")

    If this doesn't work, is it possible that you can get a screenshot of your formula so I can see the colors? Also, if you could get a screenshot of the data so I can see your column names.

    Kelly

  • Max Corbin
    Options

    I tried the way you asked and got the above.

    Below is how I had my formula...


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Try adding @cell to your = "Active /WON" term in either the SUMIF or SUMIFS. As an fyi, you can always use the plural version of the functions, even when you have only a single criteria. My personal preference is to always use the plural version in case I later need to add additional criteria.

    Using your formula, here's what I mean

    =SUMIF([Project Status]:[Project Status], @cell="Active /WON", [Project $ Value]:[Project $ Value])

    I'll be aggravated if that fixes it. Relieved but aggravated I didn't think of that soon.

    Kelly

  • Max Corbin
    Options

    Tried both the singular and plural SUMIF version of the @cell="Active /Won" and I still have


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey Max

    You are using SUMIFS plural function but the singular syntax.

    Have you tried any other text instead of Active/won? Just to test to see if something strange is going on with that data set

  • Max Corbin
    edited 02/24/21
    Options

    Just tried "Closed" and "On Hold". They did not work either. Also, I created a column formula for "Project $ Value" and deleted any cells with text. I still does not work.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    What happens if you change your Sum column to EAU? To the SUMIFS plural original version (ensure correct syntax), let's add the ISNUMBER criteria.

    =SUMIFS([Project $ Value]:[Project $ Value],[Project $ Value]:[Project $ Value], ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

  • Max Corbin
    Options

    The below formula worked:

    =SUMIFS(EAU:EAU, EAU:EAU, ISNUMBER(@cell), [Project Status]:[Project Status], "Active /WON")

    Also, it automatically took away my [ ] brackets. I entered [EAU]:[EAU] and it came back EAU:EAU. Is that normal? Now, how do we get it to work for Project $ Value?

  • Max Corbin
    Options

    Also I tried to sum the entire column for Project $ Value and it would not work but it did for EAU column.

  • Max Corbin
    Options

    OK, good news. I created a filter in the Project $ Value column for all values that are not a number and there was one that had text in it. Now it works!!! I had already done this with the other two columns but not this one. Thank you for your help!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    phew! I knew it had to be a number thing in that SUM column

    Also, to answer your question about the square brackets. Square brackets are only required in column names when the name contains a space, special character, or number. If it's just a plain word, no brackets are required.

    Sorry this took so long to figure out. Thanks for hanging with me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!