I have a column in a sharepoint list that lists the various departments within my company. One or more of these departments can be selected. Since the column does not show up as a selection when I try to use the default "group" functionality in SharePoint I exported the list to Excel hoping to get the data I want.
First column is a text column with the name of a test. The second column is the name of the group testing, this is a multi select column since multiple groups could be doing the same test. The third column is status column, Pass, Fail, Pending
I have done a countif() function to count the number of reports each group has on it's plate.
=COUNTIF(User_Group,"*" & A6 & "*")
and this works fine. When I try to do a sumproduct function I am running into issues with how to phrase the query.
=SUMPRODUCT((User_Group="*"&A6&"*")*(Test_Status="Pending"))
using the Countif you use a , to designate the criteria, in SUMPRODUCT you use <, >, or an = sign to designate. Since I am trying to designate on an "Contains" and not an = I am at a loss about how to make this work.
I would like to count how many accepted or rejected tests each group has currently. Any ideas?
