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

countif probelm?

B

bee123

New Member
Hi


I have counif problem in which excel consider the number 5-1 and 5-01 as same, and returns the count as 2,instead of count 1.

Please anyone can solve this?I tried index match also,but no use!!!!

How to sole this?
 
Hi, bee123!


What type of data contain the cells? Text, numbers, numbers as dates?

Can you please upload a sample file?


Regards!
 
Hi bee123,


Assuming that you data in present in Column A ($A$1:$A$10) and is formatted as text (if not you should format it as text) like this:

[pre]
Code: 
5-1
5-01
5-01
5-01
5-02
5-02
5-02
5-02
5-02
5-2[/pre]
..following formula will work: =SUMPRODUCT(($A$1:$A$10=$B$1)*1)

..Where B1 contains what ever you want to count in the given range.


Regards,

Faseeh
 
hi

still have the same problem.


column1 column2

7 0

7-0 0

7-01 1

7-02 1

7-2 1

7-1 1


Hi thanks for the replies.The column 1 is Text format and column 2 is General.

And the formula for column2 is =IF(COUNTIF([column1];[@column1])>1;1;0)
 
Hi bee123,


Have you tried my formula?? It is working here!! In case you want to substitue my formula in the one that is present in column 2 it should be like this:


Code: 
=IF(SUMPRODUCT(($A$1:$A$10=$B$1)*1)>1,1,0)


Regards,

Faseeh
 
Countif() will take your data as numbers and that is giving problem in your case, SUMPRODUCT() takes data as "Text" and that will work here, It will check if the cell contains the same text as you have mentioned to count for, will retrun true or false for each case, this true/false multiplied by 1 will give you 1 for every true and when sumproduct will sum, you will get the count of certain thing.


Now does this formula worked for you??


Faseeh
 
thanks Faseeh,I tried it,but i have a question,so here i have to write it as =IF(SUMPRODUCT([column1]=[column2])*1)>1,1,0) right? since Im writing the formula in column 2 ,it gives me circular reference error also. ????
 
Since because you have formatted data as table that is why it is getting difficult for you to follow things, enter this is B1 and drag down:


Code: 
=IF(SUMPRODUCT(([Column1]=Table1[[#This Row],[Column1]])*1)>1,1,0)


Note that [Column1]=Table1[[#This Row],[Column1]]
you just asked about.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top