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 M2 and F0 and the same in the 2nd row, Frizzle. For the row, 4, it should return M3 and F3, but under row, Dumbledore showing M2, F1 and Yoda M1, F2.
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 M2 and F0 and in 4, I should see M3, F2.
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
Check out the Formula Handbook template!