Array sums with partial match

[Drehinho]

Hi everyone,

I'm trying to do an array sum but I have problems because I need to use partial matchs for finding the value I want to sum.

The following example is the simplified version of the sheet I use:

In column A there are a series of codes like the following

• AC2 PO3.3 AI2.6 ME2.3
• PO3.1 PO3.5 ME2.1 ME3.4 ME3.3
• PO4.3 PO6.1 ME2.3 AI3.4
• AC1 PO2.3 AI2.2 ME2.6

you can see that each cell contains multiple codes

In column B there's a series of Yes/No/Out of Scope

What I want to do is a report sheet were for each of the values (AC1, AC2,..., ME4.7) i count the number of Yes/No/Out oc Scope.
I tried with the following:
={SUM((Sheet1!$A$2:$A$200="*AC1*")*(Sheet1!$B$2:$B$200="Yes"))}
or, considering I have all the codes I may find in column A of the sheet:
={SUM((Sheet1!$A$2:$A$200="*" &A2&"*")*(Sheet1!$B$2:$B$200="Yes"))}

However the formula doesn't seem to accept the *

Can someone help me with that?

Thank you.

 

[JamesW]

Hi there,

Welcome to the board.

Try:

=SUMPRODUCT(--(ISNUMBER(FIND("AC1",A1:A200)))*(B1:B200="yes"))

 

[Drehinho]

thanks it works