COUNTIF FUNCTIONS

Alisha Poe

I am sure this question has been answered but I have looked at so many variations that I think I am starting to confuse myself.

I am trying to make a count IF one column shows one thing but then then another column has the word HOLD in it.

So for example, I want to count the lines if Job Type says Burg and then Job Status has the word HOLD in it.

Genevieve P.

Hi @Peggy P

You would want to add a Helper Column in the source sheet in order to indicate if the row is a Child Row or a Parent Row. Then you can use this column as another Range with the criteria being if this column indicates if the row is a Child one or not.

Example formula:

=IF(COUNT(ANCESTORS([Primary Column]@row)) >= 1, "Child", "Parent")

^This says, if the number (count) of Ancestors (Parents) of this current row is greater-than-or-equal-to 1, then return the text "Child". Otherwise, it's a Parent row.

Does that make sense?

Mike Wilday

You want to use =Countifs. Its set up a little differently. Review the details below:

https://help.smartsheet.com/function/countifs

Alisha Poe

I have looked at that page about 100 times and still can't get the formula for whatever reason. It isn't counting every cell that has the word HOLD in it, whether it be SALES HOLD, HOLD or some other variation.

Mike Wilday

Try combining a countifs with a contains formula! Does this work for you?

=Countifs([Job Type]:[Job Type], "Burg", [Job Status]:[Job Status], Contains(@cell, "Hold")) should

https://help.smartsheet.com/function/contains

Alisha Poe

It is only returning a count of 1, when I have 5 on my list. It is for some reason only counting if it has only HOLD and not the others that contain the word.

Genevieve P.

Hi @Alisha Poe

I agree with Mike - the CONTAINS function should work for you in order to find cells that contain "hold" in any sort of fashion... but keep in mind that you're filtering the rows by other criteria as well (the Job Type).

Can you confirm that there are 5 with "hold" somewhere that are also the Job "Burg"? Your screen capture above only contains one that meets that criteria.

Cheers!

Genevieve

Alisha Poe

DUH!!! Sorry this is how much I have looked at it but I tried it on the next one for FIRE and when I only put the word HOLD, it returns 0 but if I put SALES HOLD, it counts 4.

Here is my formula and it returns 0:

=COUNTIFS({All Jobs Range 1}, "FIRE", {All Jobs Range 2}, CONTAINS(@cell, "HOLD"))

If I put this one in, it returns 4:

=COUNTIFS({All Jobs Range 1}, "FIRE", {All Jobs Range 2}, CONTAINS(@cell, "SALES HOLD"))

And here is my entire data set:

Mike Wilday

Yes, based on your initial requirements and the brief view you sent us in the screenshot that count should be right. Try changing the Job Type to Fire and you should get a bigger count.

Mike Wilday

@Genevieve Penny That doesn't seem like that should be the result of the first formula. Do you know why this might be happening?

Genevieve P.

@Mike Wilday & @Alisha Poe

Aha! I've got it - took me a bit to figure out.

CONTAINS needs the criteria first, then the range or the cell.

Try this:

CONTAINS("HOLD", @cell)

Alisha Poe

OMG that worked!!! Thank you guys so much!!!

Mike Wilday

@Genevieve P Whoops! I had it backward! 😊

Here's the correct formula if you want to accept it as an answer.

=Countifs([Job Type]:[Job Type], "Burg", [Job Status]:[Job Status], Contains("Hold", @cell))

Genevieve P.

Haha no worries, I do that all the time. The HAS function is the other way around, (@cell first), so I often get them mixed up.

Glad it's working for you, Alisha!

Peggy Parchert

Is it possible to do this but only counting the child rows?

Mike Wilday

You might be able to work in the children() function but I can't offhand figure that out for you right now.

https://help.smartsheet.com/function/children

Hopefully that can get you pointed in the right direction.

Genevieve P.

Hi @Peggy P

You would want to add a Helper Column in the source sheet in order to indicate if the row is a Child Row or a Parent Row. Then you can use this column as another Range with the criteria being if this column indicates if the row is a Child one or not.

Example formula:

=IF(COUNT(ANCESTORS([Primary Column]@row)) >= 1, "Child", "Parent")

^This says, if the number (count) of Ancestors (Parents) of this current row is greater-than-or-equal-to 1, then return the text "Child". Otherwise, it's a Parent row.

Does that make sense?