COUNTIFS function in the Sheet Summary Return Invalid Value

Options

Hello All,

My formula works before and all the sudden the formula doesn't work anymore. Can someone help me with the formula?

=COUNTIFS([In the Catalog]:[In the Catalog], 1, Status:Status, "For Sale", [Helper Column 1]:[Helper Column 1], "0")

In the Catalog column is a check box.

Status Column is a dropdown menu.

Helper Column 1 is to identify the Grandparent row and only count the grandparent row.

I want to count if the check box is checked, status is for sale, and it's the grandparent row.

Thank you

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    Your formula seems fine. A few things to troubleshoot:

    Is Status a multiselect dropdown or single select? If it is multi, it will not match "For Sale" if there is another option selected as well.

    You can split up your formula and try each function separately to see which column is causing the issue.

    =COUNTIFS([In the Catalog]:[In the Catalog], 1)

    =COUNTIFS(Status:Status, "For Sale")

    =COUNTIFS([Helper Column 1]:[Helper Column 1], "0")

    I don't see it being an issue, but you can try this to make sure there isn't some type of text/integer formatting issue in your helper column:

    =COUNTIFS([In the Catalog]:[In the Catalog], 1, Status:Status, "For Sale", [Helper Column 1]:[Helper Column 1], VALUE(@cell) = 0)

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hey @Emi ,


    What is the formula in [Helper Column 1]:[Helper Column 1]?

    Itai Perez

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • Emi
    Emi ✭✭
    Options

    Hello Carson,

    It looks like the status column doesn't work.

    =COUNTIFS(Status:Status, "For Sale") return invalid value.

    It works originally and I do not know why it turns out not working anymore. Is it possible to use other formula to get the same result?


    Hello Itai,

    Helper Column 1 formula is =COUNT(ANCESTORS(Status@row)). This works within my formula I get a value.

    Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!