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

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

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?

--
Biff
Microsoft Excel MVP


"Kathleen_TX" wrote in message
...
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

Beautiful, that works! Thanks a bunch!

"Tom Hutchins" wrote:

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

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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Kathleen_TX" wrote in message
...
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


.

Ok, thanks for feeding back.

--
Biff
Microsoft Excel MVP


"Kathleen_TX" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Kathleen_TX" wrote in message
...
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


.

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


"Kathleen_TX" wrote:

Beautiful, that works! Thanks a bunch!

"Tom Hutchins" wrote:

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

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" wrote:

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


"Kathleen_TX" wrote:

Beautiful, that works! Thanks a bunch!

"Tom Hutchins" wrote:

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

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

Best regards,
Kathleen

"Tom Hutchins" wrote:

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" wrote:

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


"Kathleen_TX" wrote:

Beautiful, that works! Thanks a bunch!

"Tom Hutchins" wrote:

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