Try

=SUMPRODUCT(--($J$9:$J$318="Advanced Process Control"),
--ISNUMBER(SEARCH("Chemicals",$G$9:$G$318)))

Hope this helps,

Hutch

"Kathleen_TX" wrote:

I'm trying to find a way to count the number of records that match a certain
criteria. Here's exactly what I'm working with:

Column G contains market segment data, with each cell containing multiple
market segments (Chemicals, Polymers, Specialty Chemicals, Pharmaceuticals,
etc). Column J contains application names, again with each cell containing
multiple applications (Advanced Process Control, Planning & Scheduling,
Engineering, etc).

Since I can't do a pivot table with the data setup this way, I'm trying to
find a formula that will work. For example, I want to count the number of
records that contain "Advanced Process Control" in Column J if Column G
contains "Chemicals". I tried the following formula:

=SUMPRODUCT(($G$9:$G$318="Chemicals")*($J$9:$J$318 ="Advanced Process
Control"))

But that only counts if a cell with the range in column G equals "Chemicals"
(doesn't contain anything else), not if it contains "Chemicals".

Is there a way to do an IF THEN formula, meaning that IF range G8:G318
contains "Chemicals", THEN COUNTIF (J8:J318 "Advanced Process Control"), or
is there another way to do this?

Your help will be greatly appreciated.

Best regards,
Kathleen