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.
|