[UPDATE]Need formula to count cells who's row meets specific criteria in text

Basically have 1 column with model numbers and another column with problems in text.. IE broken this, broken that. Column A has models, Example - A1234B1234C. And Column K has the problems. Example - Broken this, missing this, cracked that. Basically multiple problems in a single cell.

In column K I am only trying to match a portion of the cells value...IE 1 problem out of the many that are there (I'll make different formulas for each problem that I need to count)

I have tried the following

=SUMPRODUCT((A1:A100="A1234B1234C1234")*(K1:K100="Thispiece broken"))

Does this help?

http://www.mrexcel.com/forum/showthr...unction-NEWBIE

Yup, this formula worked great for it! Thanks much.

=SUMPRODUCT((A1:A120="Modelnumber1234")*(ISNUMBER(FIND("This Broken",K1:K120))))

---------- Post added at 11:55 AM ---------- Previous post was at 11:37 AM ----------

Now lets say... I wanted to add a 2nd Criteria in the K field....

=SUMPRODUCT((A1:A120="Modelnumber1234")*(ISNUMBER(FIND("This Broken",K1:K120)))*(ISNUMBER(Find("That Broken",K1:K120))))

Hopefully this works! Will have to test it out shortly.

*edit*
And it works! Thanks again.

---------- Post added at 12:11 PM ---------- Previous post was at 11:55 AM ----------

Just tried to use this formula to subtract ones with problems I don't want included

=SUMPRODUCT((A1:A120="Modelnumber1234")*(ISNUMBER(FIND("This Broken",K1:K120)))-(ISNUMBER(Find("That Broken",K1:K120))))

When used to get two problems it counted them correctly.

When used for 1 problem I got This=14. For two I got This+That=6. To subtract.... This-That I got -12??