COUNTIFS with Parents/Childrens AND/OR and multiple columns

Jennifer Workman
Jennifer Workman ✭✭✭
edited 04/11/20 in Formulas and Functions


I have spent HOURS on this and help.

This for "Invoice/Sheet Received" Parent column with drop downs. I am combing the Parent code AND child codes so can click+drag it down my sheet. I want to keep this. So the first part of this is for the Parent, the 2nd is in relation to the child. I keep getting Errors on my Parent side.

[Invoice/Sheet Received] - Text/Number column

[Status] - Drop Down

[#Left to use] - Numerical

EDIT: I am getting the #Invalid Date Type error. But what I am looking for is text/ in the Parent...

Here is my formula:

=IFParent@row <> "", IFCOUNTIFSCHILDREN), "Received"), "Received", IFCOUNTIFSCHILDREN), "No"), "No", IFCOUNTIFSCHILDREN), "Partial"), "Pending", IFCOUNTIFSCHILDREN), ""), "TBD")))), IFStatus@row = "Product Removed", "N/A", IFANDP2@row = "", [Product Item@row <> ""), IF# Left to use@row > 0, "Partial", IF# Used on sheets@row <> "", "Received", "No")))))

I've tried it with both COUNTIFS and COUNTIF and with/without IFCount__

Thank you SO MUCH for any and all help you can provide!!


  • rgochee
    rgochee ✭✭✭✭

    Can you provide a snapshot of your Columns used - I have built out what i think you are asking/looking for but I am not sure. Please take a look at the attached snap shot and let me know

    Status Parent Formula - =IFERROR(IF(NOT(ISBLANK([Product Item]@row)), IF(COUNTIF(CHILDREN(), "Received"), "Received", IF(COUNTIF(CHILDREN(), "No"), "No", IF(COUNTIF(CHILDREN(), "Partial"), "Pending", IF(COUNTIF(CHILDREN(), ""), "TBD"))))), "")

    Invoice/Sheet Child Formula - =IFERROR(IF(Status@row = "Product Removed", "N/A", IF(AND(ISBLANK([P2]@row), NOT(ISBLANK([Product Item]@row))), IF([# Left to use]@row > 0, "Partial", IF(NOT(ISBLANK([# Used on Sheets]@row)), "Received", "No")))), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!