# COUNTIF FUNCTIONS

Options

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.

Options

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?

«1

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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.

Options

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

• Options

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:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

edited 11/18/20
Options

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)

• Options

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

• ✭✭✭✭✭✭
Options

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

Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Hopefully that can get you pointed in the right direction.

Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!