SUMPRODUCT contains text, using asterisk

**commander1** · 11-09-2009, 01:40 PM

Let's say I have two columns:
Homeless (column A) & Services (column B)

Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab"

Here is an example:

Homeless Service
y a
y b
n a
y ab

I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))

And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks.

I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.

Thanks in advance for any help.

**NBVC** · 11-09-2009, 01:43 PM

Try

=SUMPRODUCT((A2:A5="y")*(ISNUMBER(SEARCH("a",B2:B5))))

**commander1** · 11-09-2009, 02:07 PM

That worked perfectly! Now I must learn more about the ISNUMBER and SEARCH functions and take my Excel skills to the next level.

Thanks!

**commander1** · 11-09-2009, 04:23 PM

I just wanted to let you know that I read about those two functions and I completely understand how those functions work now and produce the results I wanted. Thanks again.