| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF conundrum
Why will using the cell number like A12 in a COUNTIF formula return the value
for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF conundrum
=COUNTIF(A1:A100,"ICSC-UBS*")
"mmcap" wrote: Why will using the cell number like A12 in a COUNTIF formula return the value for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF conundrum
If you want to check if there are any duplicates, exclusive of the date
reference... Example: if A4: ICSC-UBS 4 (DEC 30) Then you want to know if any other cells in A2:A150 *begin with*ICSC-UBS 4". If that's true, then try this in a cell across from A4: =IF(COUNTIF($A$2:$A$150,MID(A4,1,FIND(" (",A4,1)-1)&"*")1,"Duplicate","") Does that help? *********** Regards, Ron XL2002, WinXP "mmcap" wrote: Why will using the cell number like A12 in a COUNTIF formula return the value for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF conundrum
That works great thank you VERY much!
Norm "Teethless mama" wrote: =COUNTIF(A1:A100,"ICSC-UBS*") "mmcap" wrote: Why will using the cell number like A12 in a COUNTIF formula return the value for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF conundrum
Yes this is EXACTLY what I was looking for. I've spent many hours banging my
head against the wall trying to accomplish what this formula does. I was getting ready to leave work when I saw the other reply and I didn't think about that it will only find matches for the ICSC-UBS. There well over 100 headings that I need to keep track of and this one formula will work for all of them. Thank you VERY much! "Ron Coderre" wrote: If you want to check if there are any duplicates, exclusive of the date reference... Example: if A4: ICSC-UBS 4 (DEC 30) Then you want to know if any other cells in A2:A150 *begin with*ICSC-UBS 4". If that's true, then try this in a cell across from A4: =IF(COUNTIF($A$2:$A$150,MID(A4,1,FIND(" (",A4,1)-1)&"*")1,"Duplicate","") Does that help? *********** Regards, Ron XL2002, WinXP "mmcap" wrote: Why will using the cell number like A12 in a COUNTIF formula return the value for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF conundrum
Thank you so much for the feedback. I'm very glad I could help.
*********** Regards, Ron XL2002, WinXP "mmcap" wrote: Yes this is EXACTLY what I was looking for. I've spent many hours banging my head against the wall trying to accomplish what this formula does. I was getting ready to leave work when I saw the other reply and I didn't think about that it will only find matches for the ICSC-UBS. There well over 100 headings that I need to keep track of and this one formula will work for all of them. Thank you VERY much! "Ron Coderre" wrote: If you want to check if there are any duplicates, exclusive of the date reference... Example: if A4: ICSC-UBS 4 (DEC 30) Then you want to know if any other cells in A2:A150 *begin with*ICSC-UBS 4". If that's true, then try this in a cell across from A4: =IF(COUNTIF($A$2:$A$150,MID(A4,1,FIND(" (",A4,1)-1)&"*")1,"Duplicate","") Does that help? *********** Regards, Ron XL2002, WinXP "mmcap" wrote: Why will using the cell number like A12 in a COUNTIF formula return the value for the number of matching text strings and using a sub formula like MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub formula instead of A12 I can use the same formula for the whole sheet even with different row headings. Without going into a long explanation the (date) needs to be in the same cell as the row heading. A B C ICSC-UBS 1 (JAN 20) ICSC-UBS 2 (Jan 27) ICSC-UBS 3 (JAN 6) ICSC-UBS 4 (DEC 30) MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain this text string. Is it because ICSC-UBS is only a portion? If so is there a way to get the COUNTIF to count the matches returned by the MID(A12,1,FIND(" (",A12,1)-3) Here is the formula in both forms. COUNTIF($A$2:$A$150,A12) COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3)) What Im ultimately trying to accomplish is to find duplicate entries that match except for the date. This formula works for exact matches but wont work with the different dates. =IF(COUNTIF($A$2:$A$150,A12)1,"Duplicate","") I just need to get the COUNTIF to work and the IF formula should work. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help!! NEW Conundrum for stats!? | Excel Discussion (Misc queries) | |||
| VBA conundrum | New Users to Excel | |||
| Calculation Conundrum | Excel Discussion (Misc queries) | |||
| ISERROR Conundrum | Excel Worksheet Functions | |||
| ISERROR Conundrum | Excel Worksheet Functions | |||
Linear Mode
