Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation in a CountIf | Excel Worksheet Functions |