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.

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.

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.

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.

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.

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.