Countif formula for multiple items in parent row
Here is my table:
In the Gender ID column I'd like to see a count of M and F in both the 1st and 2nd hierarchy rows. For example, in first row, K, it should end with M-2 and F-0 and the same in the 2nd row, Frizzle. For the row, 4, it should return M-3 and F-3, but under row, Dumbledore showing M-2, F-1 and Yoda M-1, F-2.
After I get this worked out, I'm hoping I can manipulate how it looks in the card view.
I'm very new to Smartsheet, so appreciate the guidance you provide!
Gillian
Best Answer
-
Hey
I forgot that the Ms and Fs weren't being collected the parent row. The Descendants function is what we need. This goes in any Parent row.
="M-" + VALUE(COUNTIFS(DESCENDANTS(), "M")) + ", F-" + VALUE(COUNTIFS(DESCENDANTS(), "F"))
Answers
-
Hey @Gillian
We can build the formula you request for your parent rows. Whether you need to manually insert this formula in all parent rows or whether we can build a column formula depends on how the Child row Gender results are entered.
If you are entering the Child row gender via a formula, we can incorporate this formula into a column formula that will differentiate if a row is a Parent row or a Child row. This would automatically insert the right formula into the right row to yield the desired result.
If you are manually updating the Child rows then you must manually insert the formula into any parent row. The problem is you cannot, in the same column, automatically insert a formula and also manually insert a value.
The formula to only count children, and not worry if it is a Parent row or a Child row is shown below. We don't have to worry about the 1st level and 2nd level hierarchies because every Parent row knows what rows are it's Children. Saying this a different way, we don't have to worry about differentiating children and grandchildren for what you need.
="M-"+=COUNTIFS(CHILDREN(), "M")+", F-"+COUNTIFS(CHILDREN(), "F")
If the Child row gender is being entered via formula, let me know and I'll help you build the IF statement that incorporates both formulas together
Does the formula above work for you?
Kelly
-
Kelly,
Thanks for the help.
I copied your formula and inserted in the GenderID column in the parent row. I got #invalid operation. I manually entered the gender values.
What shall I try next?
-
Hey
oops, I didn't mean to leave a parenthesis and equal in the middle of the formula. I wasn't paying attention at what I copied over. My bad - blame it on the football games currently on the TV. 😉
="M-" + VALUE(COUNTIFS(CHILDREN(), "M")) + ", F-" + VALUE(COUNTIFS(CHILDREN(), "F"))
this should now work
Kelly
-
Yes, it worked . . . mostly!
It didn't work in the first parent line. See my screenshot where I highlighted:
In K, it should be M-2 and F-0 and in 4, I should see M-3, F-2.
Sorry to pull you away from the games!
-
Hey
I forgot that the Ms and Fs weren't being collected the parent row. The Descendants function is what we need. This goes in any Parent row.
="M-" + VALUE(COUNTIFS(DESCENDANTS(), "M")) + ", F-" + VALUE(COUNTIFS(DESCENDANTS(), "F"))
-
Worked like a charm! Thanks so much.
Gillian
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!