COUNTIF FUNCTIONS
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.
Best Answer
-
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?
Answers
-
You want to use =Countifs. Its set up a little differently. Review the details below:
-
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.
-
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
-
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.
-
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
-
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:
-
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.
-
@Genevieve Penny That doesn't seem like that should be the result of the first formula. Do you know why this might be happening?
-
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)
-
OMG that worked!!! Thank you guys so much!!!
-
@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))
-
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!
-
Is it possible to do this but only counting the child rows?
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!