# Countif problem

#### Nabei

##### New Member
Hi there

currently facing a countif problem. Below is my sample data

45001245-1
45001245-2
45001245-3

45001248-1
45001248-2

For the duplicate data i.e. 45001245-1, -2, -3, i would like to set the data ending with -1 to value 1 and the rest to value 0

Currently using this formula
=IF(Countif(A\$2:A\$15,A2)=1,1,0) without the implementation of the dashes as it wouldnt work if the data has dashes.

Any help is appreciated.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
do you data always contain only "-"?

A combination of "-" and without. Sorry for not mentioning earlier

Maybe...

 A​ B​ 1​ 2​ 45001245-1​ 1​ 3​ 45001245-2​ 0​ 4​ 45001245-3​ 0​ 5​ 6​ 45001248-1​ 1​ 7​ 45001248-2​ 0​

Formula in B2 copied down
=IF(A2="","",--(COUNTIF(A\$2:A2,LEFT(A2,SEARCH("-",A2)-1)&"*")=1))

Hope this helps

M.

You may try the following if you always have "-??" in the end

=IF(RIGHT(A1,2)="-1",SUBSTITUTE(A1,"-",""),REPLACE(A1,FIND("-",A1),9,0))

do you data always contain only "-"?

For cases without "-", how do you want to treat the data?

A combination of "-" and without. Sorry for not mentioning earlier

A combination of "-" and without. Sorry for not mentioning earlier

Maybe...

 A​ B​ 1​ 2​ 45001245-1​ 1​ 3​ 45001245-2​ 0​ 4​ 45001245-3​ 0​ 5​ 6​ 45001248-1​ 1​ 7​ 45001248-2​ 0​ 8​ 12345​ 1​ 9​ 12345​ 0​ 10​ AB456​ 1​ 11​ AB456​ 0​

Formula in B2 copied down
=IF(A2="","",--(COUNTIF(A\$2:A2,IF(ISNUMBER(A2),A2,LEFT(A2,IFERROR(SEARCH("-",A2)-1,LEN(A2)))&"*"))=1))

M.

So i meddled a bit with it.

If the data is switched around, i.e

45001245-3
45001245-1
45001245-2

the data which ends with "-3" returns a value 1 instead of 0 while the data which ends with "-1" returns a value 0

What are you looking for - a unique count that must equal 2 for the sample posted?

Set a count for the data such that those that ends with "-1" (45001246-1) return a value of 1, and the rest (45001246-2, 45001246-3, 45001246-3) return a value of 0. hope that clarifies things

Replies
19
Views
827
Replies
9
Views
416
Replies
11
Views
443
Replies
21
Views
949
Replies
0
Views
252

1,221,129
Messages
6,158,106
Members
451,465
Latest member
Big_Bill62

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back