Countif formula for multiple items in parent row

Gillian

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

Kelly Moore

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"))

Kelly Moore

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

Gillian

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?

Kelly Moore

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

Gillian

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!

Kelly Moore

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"))

Gillian

Worked like a charm! Thanks so much.

Gillian