I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the records
that contain €œXX€� anyplace witihin Column A plus have €œXXXX€� in Column B.

I tried countif statements with no success. Any ideas?

One way ..

Assuming data in cols A and B are within A1:B100

With the values listed say,
in D1: XX, in E1: XXXX

we could put in F1:
=SUMPRODUCT((ISNUMBER(SEARCH(TRIM(D1),$A$1:$A$100) )*ISNUMBER(SEARCH(TRIM(E1),$B$1:$B$100))))

SEARCH is not case sensitive. We could replace SEARCH with FIND if case
sensitivty is required for the values listed in D1:E1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mary-Lou" wrote:
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the records
that contain €œXX€� anyplace witihin Column A plus have €œXXXX€� in Column B.

I tried countif statements with no success. Any ideas?

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B.

I tried countif statements with no success. Any ideas?

Thanks. When I enter the array forumula, the cell value displays zero
(which is incorrect). But when I edit the function, the 'formula results"
displays 163 (which is correct). Why wouldn't the 163 display in the cell?

"Max" wrote:

One way ..

Assuming data in cols A and B are within A1:B100

With the values listed say,
in D1: XX, in E1: XXXX

we could put in F1:
=SUMPRODUCT((ISNUMBER(SEARCH(TRIM(D1),$A$1:$A$100) )*ISNUMBER(SEARCH(TRIM(E1),$B$1:$B$100))))

SEARCH is not case sensitive. We could replace SEARCH with FIND if case
sensitivty is required for the values listed in D1:E1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mary-Lou" wrote:
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the records
that contain €œXX€� anyplace witihin Column A plus have €œXXXX€� in Column B.

I tried countif statements with no success. Any ideas?

"Mary-Lou" wrote:
Thanks. When I enter the array forumula, the cell value displays zero
(which is incorrect). But when I edit the function, the 'formula results"
displays 163 (which is correct). Why wouldn't the 163 display in the cell?

Don't remember saying it is an array formula, requiring CSE <g.

Just press normal ENTER to confirm the formula,
after pasting/placing it within the formula bar

(just correct the inadvertent line breaks in the formula
when you copy paste it direct from the post)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks. I'm getting the same results when I tried the array Max included - 0
value displays within the cell, but when editting the function, the formula
results display a value.

The sentences within Column A can be really wonky and could be very long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B.

I tried countif statements with no success. Any ideas?

I tried it both ways - same results.

"Max" wrote:

"Mary-Lou" wrote:
Thanks. When I enter the array forumula, the cell value displays zero
(which is incorrect). But when I edit the function, the 'formula results"
displays 163 (which is correct). Why wouldn't the 163 display in the cell?

Don't remember saying it is an array formula, requiring CSE <g.

Just press normal ENTER to confirm the formula,
after pasting/placing it within the formula bar

(just correct the inadvertent line breaks in the formula
when you copy paste it direct from the post)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in Column
B.

I tried countif statements with no success. Any ideas?

"Mary-Lou" wrote:
I tried it both ways - same results.

Perhaps you could paste the actual formula you're using ??
(assume you've adapted it)

In adapting to suit, pl note that entire col references (eg: A:A, B:B)
cannot be used in SUMPRODUCT, and the ranges used should be identical in
size, eg: $A$1:$A$1000, $B$1:$B$1000
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hey, I did finally get it to work! I had copied the forumla several times
in my worksheet and D1 and E1 values had been adjusted in the copies - so
once I put them back to what they should have been - BINGO! I will make them
absolute so they don't change.

Thanks very much.

"Mary-Lou" wrote:

I tried it both ways - same results.

"Max" wrote:

"Mary-Lou" wrote:
Thanks. When I enter the array forumula, the cell value displays zero
(which is incorrect). But when I edit the function, the 'formula results"
displays 163 (which is correct). Why wouldn't the 163 display in the cell?

Don't remember saying it is an array formula, requiring CSE <g.

Just press normal ENTER to confirm the formula,
after pasting/placing it within the formula bar

(just correct the inadvertent line breaks in the formula
when you copy paste it direct from the post)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in Column
B.

I tried countif statements with no success. Any ideas?

"Mary-Lou" wrote:
Hey, I did finally get it to work! I had copied the formula several times
in my worksheet and D1 and E1 values had been adjusted in the copies - so
once I put them back to what they should have been - BINGO! I will make them
absolute so they don't change.
Thanks very much.

You're welcome !
Glad you finally got it up working over there <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in
Column
B.

I tried countif statements with no success. Any ideas?

Hi. I was able to get Max's formula to work, but I am unable to get yours to
work. The two functions are different. I'm still working on getting your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in
Column
B.

I tried countif statements with no success. Any ideas?

I finally have the following 2 separate functions working but still working
on putting them together. Here's what I have:

Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))))
(looking for any row that contains "AM " within the column)
Part 2: =SUMPRODUCT(--(H9:H600="channels"))

Here's what I have as one formula - that still returns 0:
=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))


"Mary-Lou" wrote:

Hi. I was able to get Max's formula to work, but I am unable to get yours to
work. The two functions are different. I'm still working on getting your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in
Column
B.

I tried countif statements with no success. Any ideas?

OK - I finally got it working. Apparently the functions are really picky
about where the rows start. I entered row 9 in the formula, but had inserted
2 extra blank rows in 9 and 10, moving the data down to row 11. I decided to
re-enter the range into the formula and voila - it worked.

Thanks for your help and patience.



"Mary-Lou" wrote:

I finally have the following 2 separate functions working but still working
on putting them together. Here's what I have:

Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))))
(looking for any row that contains "AM " within the column)
Part 2: =SUMPRODUCT(--(H9:H600="channels"))

Here's what I have as one formula - that still returns 0:
=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))


"Mary-Lou" wrote:

Hi. I was able to get Max's formula to work, but I am unable to get yours to
work. The two functions are different. I'm still working on getting your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in
Column
B.

I tried countif statements with no success. Any ideas?

The two functions are different

Upon closer inspection, yes, they are.

In your sample data you say:

Example of values in Column B:
AAAA
BBBB
BBBB

I assumed that those are representative values and my formula looks for a
specific value "XXXX".

Max's formula looks for a specific string:
ISNUMBER(SEARCH(TRIM(E1),$B$1:$B$100))))

So, either your column B has values like:

Custom XXXX Upgrade
Printing reports on XXXX
Custom YYYY Upgrade

Or, if your column B values really a

Example of values in Column B:
AAAA
BBBB
BBBB

Then you have unseen characters like leading and/or trailing spaces:

<spaceAAAA
BBBB<space
XXXX<space
<spaceXXXX

Biff

"Mary-Lou" wrote in message
...
Hi. I was able to get Max's formula to work, but I am unable to get yours
to
work. The two functions are different. I'm still working on getting your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas
is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on
the
seond one (but when editting the function, the formula displays a
value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function, the
formula
results display a value.

The sentences within Column A can be really wonky and could be very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting
the
records
that contain "XX" anyplace witihin Column A plus have "XXXX" in
Column
B.

I tried countif statements with no success. Any ideas?

That doesn't look anything like your original post! <BG

=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))

I assume that's an intentional space after the M: "*AM *"

You don't need wildcards with SEARCH:

=SUMPRODUCT(--(ISNUMBER(SEARCH("AM ",E9:E600))),--(H9:H600="channels"))

Biff

"Mary-Lou" wrote in message
...
I finally have the following 2 separate functions working but still working
on putting them together. Here's what I have:

Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))))
(looking for any row that contains "AM " within the column)
Part 2: =SUMPRODUCT(--(H9:H600="channels"))

Here's what I have as one formula - that still returns 0:
=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))


"Mary-Lou" wrote:

Hi. I was able to get Max's formula to work, but I am unable to get
yours to
work. The two functions are different. I'm still working on getting
your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas
is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on
the
seond one (but when editting the function, the formula displays a
value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function,
the
formula
results display a value.

The sentences within Column A can be really wonky and could be
very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various
values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only
counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX"
in
Column
B.

I tried countif statements with no success. Any ideas?

Ignore the heading...<G

I confirm that Max's function works for me:

Try this:

With the text in col A (range named text)
and the codes in column B (range named codes),

then create a matrix of possible combinations, starting in:
D2 (and down), for text variables (eg XX)
E1 (and across), for codes, eg AAAA

Then in E2, type:

=SUMPRODUCT(--(ISNUMBER(SEARCH($D2,text))),--(codes=E$1))

then copy this cell down the column range, then
copy that range across the rest of the matrix. No array entry
required.

I'm hoping this will also give you the right answer, and many thanks to
Max for the work.

I overlooked using the wildcard in the search function, but it worked
anyway.

Regards
Mike


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=547699

In my post, I was just giving a generic example. Without the wildcard
(including the space) I got too many hits, for example, just searching for
"am" on it's own would pick up the letters embedded within words - but I only
wanted them on their own.

"Biff" wrote:

That doesn't look anything like your original post! <BG

=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))

I assume that's an intentional space after the M: "*AM *"

You don't need wildcards with SEARCH:

=SUMPRODUCT(--(ISNUMBER(SEARCH("AM ",E9:E600))),--(H9:H600="channels"))

Biff

"Mary-Lou" wrote in message
...
I finally have the following 2 separate functions working but still working
on putting them together. Here's what I have:

Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))))
(looking for any row that contains "AM " within the column)
Part 2: =SUMPRODUCT(--(H9:H600="channels"))

Here's what I have as one formula - that still returns 0:
=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels"))


"Mary-Lou" wrote:

Hi. I was able to get Max's formula to work, but I am unable to get
yours to
work. The two functions are different. I'm still working on getting
your
version up and running... it's bugging me now why it won't work.

"Biff" wrote:

I'm confused.........

You're reply to Max says you got it working. Is it working or not?

The only difference, other than syntax, between mine and Max's formulas
is
the use of the TRIM function on the criteria cells.

Biff

"Mary-Lou" wrote in message
...
I broke it out, and I get results with the first one OK, but get 0 on
the
seond one (but when editting the function, the formula displays a
value).

"Biff" wrote:

Hi!

Try breaking the formula into 2 formulas and see what you get:

Formula to count just column A:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))))

Formula to count just column B:

=SUMPRODUCT(--(B1:B100="XXXX"))

Do either of these return 0?

Biff

"Mary-Lou" wrote in message
...
Thanks. I'm getting the same results when I tried the array Max
included - 0
value displays within the cell, but when editting the function,
the
formula
results display a value.

The sentences within Column A can be really wonky and could be
very
long -
can the funny results be because of the data within Column A?

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX"))

Biff

"Mary-Lou" wrote in message
...
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various
values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only
counting the
records
that contain "XX" anyplace witihin Column A plus have "XXXX"
in
Column
B.

I tried countif statements with no success. Any ideas?

This way worked for me, i had to use it on a spreed sheet i was doing.
ISNUMBER gives true/false and so does B1:B10="Closed". So instead of
adding those together, multiply (true*true=1,true*false=0,ect.), so it would
look like this :

=SUMPRODUCT(ISNUMBER(SEARCH("this",A3:A30))*(J3:J3 0="closed"))

That is just an example, "this" being part of the line like your XX or YY,
and "closed" being the exact line.

Hope this brings you closer to your conclusion! : )

Thanks so much. This is a great forum.

"Robert Brown" wrote:

This way worked for me, i had to use it on a spreed sheet i was doing.
ISNUMBER gives true/false and so does B1:B10="Closed". So instead of
adding those together, multiply (true*true=1,true*false=0,ect.), so it would
look like this :

=SUMPRODUCT(ISNUMBER(SEARCH("this",A3:A30))*(J3:J3 0="closed"))

That is just an example, "this" being part of the line like your XX or YY,
and "closed" being the exact line.

Hope this brings you closer to your conclusion! : )