I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

Thanks, but this value needs to go into a report and this approach is
cumbersome to do that. I also will need to do this for many other
combinations and that would take some time.

Is there anything else out there? This can't be THAT hard for MS!!

Thanks very much, though!

"CLR" wrote:

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

The easiest way would be to use a filter and VBA, it is much more cumbersome
to use formulas on 40000 rows of data.



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
Thanks, but this value needs to go into a report and this approach is
cumbersome to do that. I also will need to do this for many other
combinations and that would take some time.

Is there anything else out there? This can't be THAT hard for MS!!

Thanks very much, though!

"CLR" wrote:

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often
the
two occur together. In col X will occur the word "warranty" and in
column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set
of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

Rog wrote...
I need to use two columns and search for two things and count
how often the two occur together. In col X will occur the word
"warranty" and in column AD will occur, for example the word
"switch". The problem is that the word switch will occur in a
paragraph, so I need a wild card and SUMPRODUCT does not support
this. . . .

FWIW, only SUMIF, COUNTIF, SEARCH, MATCH and {V|H}LOOKUP support
wildcards, and the last 3 only for exact matching. However, if you're
looking for a particular word that would be separated from other text
by spaces, you don't need wildcards.

=SUMPRODUCT(COUNTIF(Range,{"test *","* test *","* test"}))

and

=SUMPRODUCT(--ISNUMBER(SEARCH(" test ",Range)))

return the same result. The array argument to COUNTIF in the first
formula is necessary to capture "test" appearing at the start or end
of each cell value in Range as well as appearing in the middle of the
string. Eliminating the spaces would mean you could match "test" as a
substring of other words, e.g., "detested". So for more rigorous
matching, SUMPRODUCT/ISNUMBER/SEARCH is actually simpler to use. And
as an added bonus, SEARCH allows you to use wildcards if you have to.

One way:

=SUMPRODUCT(--(X1:X1000="warranty"),
--(ISNUMBER(SEARCH("switch",AD1:AD1000))))


In article ,
Rog wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

If you have 40000 records to search then I doubt SUMPRODUCT is the right
tool but you can use it to find strings that are part of other strings, de
facto wildcard

=SUMPRODUCT(--(ISNUMBER(SEARCH("warranty",A2:A40000))),--(AD2:AD40000="Switch"))



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

I think you're missing the point here. I need to count the rows that have
both "warranty" and "switch".
col X col AD
warranty the switch broke
customer light
warranty the valve is bad
customer the switch failedh
warranty it was the switch that broke

The result desired here is 2 because two rows have "warranty" AND the word
"switch". Hope that clarifies it and thanks again!







"Peo Sjoblom" wrote:

If you have 40000 records to search then I doubt SUMPRODUCT is the right
tool but you can use it to find strings that are part of other strings, de
facto wildcard

=SUMPRODUCT(--(ISNUMBER(SEARCH("warranty",A2:A40000))),--(AD2:AD40000="Switch"))



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If
I add the word "switch" to one of the records it will not update to show the
count plus one. Please advise. What is happening here? I have put the word at
the beginning of the record and inthe middle of it. I am using "*" before and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

Rog wrote:
I think you're missing the point here. I need to count the rows that have
both "warranty" and "switch".
col X col AD
warranty the switch broke
customer light
warranty the valve is bad
customer the switch failedh
warranty it was the switch that broke

The result desired here is 2 because two rows have "warranty" AND the word
"switch". Hope that clarifies it and thanks again!
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
consider something like the following:

Assuming your lookup values are in X1:X5, Array enter into AE1:AE5

=SEARCH("switch",VLOOKUPs("warranty",X1:AD5,7),1) and enter into AF1

=COUNTIF(AE1:AE5,"<#VALUE!")

The result should be in AF1

Alan Beban

Alan Beban <unavailable wrote...
....
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,
you might consider something like the following:

Assuming your lookup values are in X1:X5, Array enter into AE1:AE5
=SEARCH("switch",VLOOKUPs("warranty",X1:AD5,7), 1)
and enter into AF1
=COUNTIF(AE1:AE5,"<#VALUE!")

The result should be in AF1

The OP did mention that his data spans nearly 40K rows. The good news
is that there'd be only one udf VLOOKUPS call, and since its result
would presumably have far fewer than 40K entries, there'd effectively
be fewer SEARCH calls.

But why bother with entering an array formula in AE1:AE#? The SEARCH
will return an array of numbers or error values. All that'd be needed
is the SINGLE array formula

=COUNT(SEARCH("switch",VLOOKUPS("warranty",X1:AD#, 7)))

More efficient array formulas could be used that don't require udfs.

=COUNT(IF(X1:X#="warranty",SEARCH("switch",AD1:AD# )))

This will do less work than the VLOOKUPS formula much more quickly
since it avoids the Excel/VBA interface.

Note: replace # with the actual ranges' bottom row number.

You can use DCOUNT by setting the criteria text to *switch*.

"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!