Counting based on multiple criteria

[Kathleen_TX]

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

 

[Tom Hutchins]

Try

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

Hope this helps,

Hutch

 

[T. Valko]

I want to count the number of records that contain

"Advanced Process Control" in Column J if Column
G contains "Chemicals".

Would Specialty Chemicals be counted as Chemicals?

 

[Kathleen_TX]

Beautiful, that works! Thanks a bunch!

Try

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

Hope this helps,

Hutch

 

[Kathleen_TX]

Yes, I realized that later. But I worked around it by doing a Find and
Replace to replace all occurrences of Specialty Chemicals with Spec.

Tom Hutchins suggestion worked like a charm. Thanks!

 

[T. Valko]

Ok, thanks for feeding back.

 

[Kathleen_TX]

Sorry, but I just realized that the function didn't exactly work as expected.
The first part of the function is only returning the number of records that
exactly match the criteria (e.g., if a cell only contains "Advanced Process
Control" and nothing else). It's not picking up records that contain the
criteria in addition to other text strings. For example, some cells contain
Advanced Process Control in addition to other text strings (e.g. cell J86
contains "Exploration & Production Operations, Process Engineering, Advanced
Process Control, Production Management & Execution"). I need the function to
count this cell since it contains "Advanced Process Control". Is there a way
to do this?

Many thanks in advance for your help.

Best regards,
Kathleen

 

[Tom Hutchins]

I guess I misunderstood what you wanted. If you want to count all the cells
that have "Chemicals" anywhere in them in column G AND have "Advanced Process
Control" anywhere in them in column J, try this version:

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

Hope this helps,

Hutch

 

[Kathleen_TX]

Thanks Tom. It works beautifully. You're amazing.

Best regards,
Kathleen