countif and concatenate
 

I am having trouble with the COUNTIF function. I am searching for the
frequency of occurence for a name in a range of cells (not in a row or
left-most column). The name I am searching for is the result of a cell that
has been concatenated from other data.

Example:
Cell A43 reads: =CONCATENATE('Contact Info'!B2," ",'Contact Info'!A2)
resulting in the cell populating with a name (i.e. Shelly Bell)

I want to determine how many times that specific name repeats in data found
elsewhere in the worksheet. These results will be displayed in C43; the
search is occurring C10:C38.

I have tried =COUNTIF (C10:C38, "A43") but xl looks for A43 as the text.
I have tried =COUNTIF(A43,C10:C38) the other way and the formula box shows
"Shelly Bell" as the results of A43; however, the results still come back as
zero (0).

Frustration is setting in...please HELP!!
--
J. Paul Long
Training Manager

countif and concatenate
 

Try this:

=SUMPRODUCT(--(C10:C38=A43))


"JP Long" wrote:

I am having trouble with the COUNTIF function. I am searching for the
frequency of occurence for a name in a range of cells (not in a row or
left-most column). The name I am searching for is the result of a cell that
has been concatenated from other data.

Example:
Cell A43 reads: =CONCATENATE('Contact Info'!B2," ",'Contact Info'!A2)
resulting in the cell populating with a name (i.e. Shelly Bell)

I want to determine how many times that specific name repeats in data found
elsewhere in the worksheet. These results will be displayed in C43; the
search is occurring C10:C38.

I have tried =COUNTIF (C10:C38, "A43") but xl looks for A43 as the text.
I have tried =COUNTIF(A43,C10:C38) the other way and the formula box shows
"Shelly Bell" as the results of A43; however, the results still come back as
zero (0).

Frustration is setting in...please HELP!!
--
J. Paul Long
Training Manager

countif and concatenate
 

Another option ..
Try removing the double quotes around the cell ref ("A43")
i.e. put in C43: =COUNTIF (C10:C38, A43)
It should work ..

And the formula in A43 could also be simplified a little by using the
ampersand operator "&" which is equiv to CONCATENATE,
i.e. in A43: ='Contact Info'!B2 & " " & 'Contact Info'!A2
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JP Long" wrote in message
...
I am having trouble with the COUNTIF function. I am searching for the
frequency of occurence for a name in a range of cells (not in a row or
left-most column). The name I am searching for is the result of a cell
that
has been concatenated from other data.

Example:
Cell A43 reads: =CONCATENATE('Contact Info'!B2," ",'Contact Info'!A2)
resulting in the cell populating with a name (i.e. Shelly Bell)

I want to determine how many times that specific name repeats in data
found
elsewhere in the worksheet. These results will be displayed in C43; the
search is occurring C10:C38.

I have tried =COUNTIF (C10:C38, "A43") but xl looks for A43 as the text.
I have tried =COUNTIF(A43,C10:C38) the other way and the formula box shows
"Shelly Bell" as the results of A43; however, the results still come back
as
zero (0).

Frustration is setting in...please HELP!!
--
J. Paul Long
Training Manager

countif and concatenate
 

You ROCK! Just tried it out and it works great. Tell me... what is the
purpose of the "- -" at the beginning of the function?
--
J. Paul Long
Training Manager


"Elkar" wrote:

Try this:

=SUMPRODUCT(--(C10:C38=A43))


"JP Long" wrote:

I am having trouble with the COUNTIF function. I am searching for the
frequency of occurence for a name in a range of cells (not in a row or
left-most column). The name I am searching for is the result of a cell that
has been concatenated from other data.

Example:
Cell A43 reads: =CONCATENATE('Contact Info'!B2," ",'Contact Info'!A2)
resulting in the cell populating with a name (i.e. Shelly Bell)

I want to determine how many times that specific name repeats in data found
elsewhere in the worksheet. These results will be displayed in C43; the
search is occurring C10:C38.

I have tried =COUNTIF (C10:C38, "A43") but xl looks for A43 as the text.
I have tried =COUNTIF(A43,C10:C38) the other way and the formula box shows
"Shelly Bell" as the results of A43; however, the results still come back as
zero (0).

Frustration is setting in...please HELP!!
--
J. Paul Long
Training Manager

countif and concatenate
 

JP Long wrote...
....
Cell A43 reads: =CONCATENATE('Contact Info'!B2," ",'Contact Info'!A2)
resulting in the cell populating with a name (i.e. Shelly Bell)

I want to determine how many times that specific name repeats in data found
elsewhere in the worksheet. These results will be displayed in C43; the
search is occurring C10:C38.

I have tried =COUNTIF (C10:C38, "A43") but xl looks for A43 as the text.
I have tried =COUNTIF(A43,C10:C38) the other way and the formula box shows
"Shelly Bell" as the results of A43; however, the results still come back as
zero (0).

Have you tried

=COUNTIF(C10:C38,A43)

?