• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count the cell just once, even when its repeated many time is the row

I

Imran Sait

New Member
Hi All,

I am not an excel expert but I have encountered a small problem and I am stuck here trying to get a solution.
My question is I have a row which contains data which are both text and numerals, and the values are repeated in the row. I need to count the cell just once even if it is repeated many times. if the cell comes 1st time I need value as "1" and if it repeats 2nd or 3rd time it should return value as "0".
For example see the row below

Data The values I need
a12 1
b11 1
a11 1
c45 1
d23 1
a12 0
c45 0
e34 1
b11 0
a12 0
d23 0

I have tried to use countif and sumprod but didnt not get what I requeired.
Please help
 
Thanks a lot Harish, It worked just as I wanted. but can you tell me how is the function COUNTIF($A$1:$A1,A1)=1 helping me
 
@Imran Sait

COUNTIF($A$1:$A1,A1)=1

In this function Blue is the dynamic expanding range since row 1 is locked $A$1 and to range row is not locked $A1 so it will count from A1 to A1 value is in A1 so it will give 1.

But when you copy this formula to row 2 the range will become $A$1:$A2 and value will be of A2 so if A2 value is same as A1 countif will give 2 so IF function will put a 0 in that row only when the count is 1 it will put a 1.

and the same gets repeted for each row. Expanding range is used in many functions and is a great tool.

Hope the explanation is clered to you.

Regards,
 
Imran,

I am glad to know that this function helps you! All the credit goes to Chandoo.org team and blog.

I am also a learner and learned it form the Chandoo.org. The logic of this function can be explained by the experts of Chandoo.org.
 
Thanks again Misra Ji, I can tell I have learned something new, I had an idea that i should use COUNTIF but never thought of IF formula.
Thanks alot
 
You must log in or register to reply here.
Back
Top