yogi_Count Number Of Cases For Specified Group Where Status Column Contains Specified Phrase

                                          Google Spreadsheet   Post  #1221
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 30, 2013
user Clare-Noel Holinghaus (http://productforums.google.com/forum/?zx=7m7yxfezh113#!category-topic/docs/spreadsheets/oMozBKn-1mY)
Wildcard workaround with additional filtering
I'm trying to figure out a formula that will help me figure out how many cases that were assigned to a group contain "Closed" as a part of their current status. The problem I'm running into is that Google Spreadsheets does not have a wildcard symbol. I'm trying to use workaround functions, but I don't know how to combine them with the additional filtering to limit the result to only cases completed by a particular group.

For example, below I would be trying to figure out how many cases completed by Group B contain "Closed" as a part of the status.

Case               Group                Status
1                     A                       Closed: Complete
2                     B                       Open: Needs Follow Up
3                     A                       Open: Work In Progress
4                     B                       Closed: Complete
5                     B                       Closed: Canceled
6                     B                       Open: Work In Progress

The formula should return "2" in the above example.

I can't separate out the Status descriptors, so I am just looking for one formula that can get at this intersection of data.

The two formulas I have been looking at are =(COUNTIF( arrayformula(ISNUMBER(search(criteria, range))),TRUE)) and =COUNTIF(FILTER(array ,range=criteria), criteria)
If anyone knows a good way to combine these two, or can figure out an alternate formula, I would be very appreciative!

Thanks!

Clare

-------------------------------------------------------------------------------------------------------