Here is one way. I created a "duplicated range" that only counted when a value was duplicated along the row regardless of the number of identical items that were in a row.
Formula:
=IF(AND(B2>0,B2=C2,COUNTIF($B$2:B2,B2)=1),1,"")
Then sum the 1s created by that duplicated range:
Formula:=SUM(O2:X2)
This is how it came together on a worksheet
There is probably a better way of doing this.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you for your appearance and response.Originally Posted by newdoverman
This is a solution with extra columns.
Is there a possibility to solve counting using a single formula in a one cell?
If you don't want many columns One formula can be
=SUM(IF(B2:K2=A2:J2,IF(B2:K2<>C2:L2,1,"")))
in cell M2 copied down..
However, it an array formula so to use it just press ctrl+shift+enter instead after just enter after pasting this formula in the cell...
note-this is for the second case i.e. counting twice
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
Thank you for answer
This array formula does not work well. Returns wrong results
This is results which I need in M column
2
1
1
3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Dumy 
Register To Reply
Bookmarks