COUNTIFS with Parents/Childrens AND/OR and multiple columns
Hello,
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!!
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!