COUNTIFS function in the Sheet Summary Return Invalid Value

Emi

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

Carson Penticuff

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 Perez

Hey @Emi ,

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

Emi

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