COUNTIFS with a multiple Dropdown selector not working with multiple values in one cell

[xdresch]

So i'm trying to get the formula to count how many of these tasks a person has done when you select the criteria. It works whenever I keep the cell value as a singular item. However, having (AS, NK) doesn't get counted, in that circumstance 2 people did the task together.
Is there a way to get the formula to count if "AS" appears within column 2 correlating to column 1's task selection.

The formula i'm using is =COUNTIFS(C16:C26,F15,B16:B26,G15)

 

[tyija1995]

Hey there,

try:

=SUMPRODUCT((B16:B26=G15)*(ISNUMBER(FIND(F15,C16:C26))))

Change the FIND function to SEARCH if you are not concerned with the casing of the string in cell F15.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try a COUNTIFS formula with a structure like this

20 03 31.xlsm
	A	B	C	D	E	F	G
4	EF	AS, NK			AS	EF	3
5	EF	AS
6	EF	AS
7	Ef	DE
COUNTIFS

Cell Formulas
Range	Formula
G4	G4	=COUNTIFS(B4:B7,"*"&E4&"*",A4:A7,F4)