Countif containing specific text

Paul Newcome

@StaceyFC What column type are you using, and what is your existing formula?

StaceyFC

Column type is Text/Number

=COUNTIFS({Contract Clause List Master Present State}, "Active", {Contract Clause Child Count}, 0, {Contract Clause List Team}, FIND("Services", @cell) > 0)

The contract dashboard data sheet compiles a count of all the teams who have clauses from a contract clause list master sheet. For example, the teams in question are General Services, Managed Services and Services.

When the formula above computes, the FIND function will include the count of all three of those teams into one rather than only counting the number of clauses for just the word "Services". What I want to know is how do I adjust the FIND portion to pull an exact match to the word "Services" rather than taking into account the other two teams that contain the word, Services?

Paul Newcome

@StaceyFC Sorry. I meant what column type for the column containing "Services", and can there be more than one selection made within the same cell.

StaceyFC

The way the sheet and column are set up, we have a parent row for each clause which can, and usually does, contain multiple selections. We then have child rows under each parent each of which will only have one selection identified.

Paul Newcome

@StaceyFC Are you able to provide a screenshot?

StaceyFC

Are you asking about the Dashboard Data sheet specifically or are you asking about the other sheet references as listed within the formula?

=COUNTIFS({Contract Clause List Master Present State}, "Active", {Contract Clause Child Count}, 0, {Contract Clause List Team}, FIND("Services", @cell) > 0)

• Contract Clause List Master - this is where all compliance clauses are entered with a parent row and a child row. The parent row allows for a drop down within the teams (to include General Services, Managed Services and Services). Child rows are tasks assigned to a specific team for that clause (allows for a drop down to include General Services, Managed Services and Services.)
• Contract Clause Child Count: This counts all child rows that pertain to the specific team in order to compile the total count for each team on the Dashboard Data Sheet.
• Contract Clause List Team: drop down of list of teams to include General Services, Managed Services and Services.

The Dashboard Data Sheet that is used to compile the Compliance Dashboard is listed by team and then how many tasks are assigned to that team. Because the word "services" appears in General Services, Managed Services and Services, that COUNTIFS function is including those counts for just the Services to include General and Managed Services. In other words, if there is just 16 Services, 3 General Services and 1 Managed Services, the COUNTIFS as written currently is rendering 10 tasks for Services rather than just 16.

JamesQ4

Hi @Paul Newcome & SS Community

I am after your assistance, I have a 'CountIFS' issue and your comments on this thread seem to resolve related queries.

The goal of my formula is to show how many times an Individual is assigned to an In Progress task that contains the word 'Tactic' . In total it has three references.

I am able to complete the first two using the below:

=COUNTIFS({Assigned To}, "JamesQ4", {Status}, "In Progress")

However, could you help direct me to what I need to update to find where a person is assigned to tasks that contain the word 'Tactic' in and they are 'In Progress', at the moment i have:

=COUNTIFS({Assigned To}, "Bill Prew", {Status}, "In Progress"), FIND("Tactic" , {Tasks}) >0)

The tasks will be titled "Tactic #1 - xyz" so its having a formula that can pick up the word 'tactic'.

Any help of guidance from other members is also greatly appreciated.

Thanks in advance

Paul Newcome

@JamesQ4 Try this:

=COUNTIFS({Assigned To}, "Bill Prew", {Status}, "In Progress", {Tasks}, FIND("Tactic", @cell) >0)

JamesQ4

Thank you @Paul Newcome that is brilliant and certainly helps add more value into our dashboard visuals.