Search code examples
excelstringtextformulacountif

Excel Cell reference of a text string + Wildcard?

I have a list of cell with different conditions under a column called "Status".

I am trying to make a summary COUNTIF for each occurrence of a status. Single mentions are easy, like "Passed" or "Not Started".

=COUNTIFS(RawData[Training Course],[@[Training Period]],RawData[Status],"Passed")

The issue is "In Progress", where the string always follows the format of "In Progress (x/x)". Example would be "In Progress (0/5)" or "In Progress 3/6", depending on how many tasks need to be completed.

I don't want to have to create COUNTIFS conditions for EVERY variation of that cell. I just want it to look at "In Progress", and ignore any following text in that cell.

This is what I tried so far, which results in a "0".

=COUNTIFS(RawData[Training Course],[@[Training Period]],RawData[Status],"In Progress"&" "&"(*)",RawData[Status],"Not Started")

I also tried using SEARCH() command, though that results in a #SPILL error.

Any ideas?

Solution

  • I just had a coworker assist and recommend breaking up the COUNTIFS in two COUNTIF separated by +.

    Then I just needed to wrap the entire statement in wildcards "*In Progress*". Typical me, drastically overthinking the problem!