Hi,
See if these meet your needs:
=IF(A2=A1,"",COUNTIF(A$2:A$13,A2))
=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,"x")=1,IF(B2="x",COUNTIFS(A2:A$13,A2,B2:$B$13,"x"),""),"")
In both cases I am assuming the data starts on row 2. Adjust the references
to suit.
The first solution works in 2003 or 07, the second one only in 2007
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Christopher Naveen" wrote:
> Hi,
>
> I have 2 cols and i want to count the duplicates comparing 2nd col values, i
> am requesting you to read the below example to understand better.
>
> Ex :
>
> A B C D
> ABC Y 5
> ABC X 4
> ABC X
> ABC X
> ABC X
> DEF Y 7
> DEF Y
> DEF Y
> DEF X 2
> DEF Z
> DEF Z
> DEF X
>
> Pls. refer the above ex, col A has values repeated and col B has some value
> like X, Y, Z. I am trying to count the values in col A and mention the count
> at the 1st occurence. Pls. refer col. C (Total ABC count is 5 so it is
> returning the value in the 1st line, same way DEF count is 7 and it returns
> count in the 1st accurence (6th Row) so iused the formula as
> =if(countif(A$:A2,A2)>1,"",countif($A$2:$A$100,A2)) and it worked fine.
>
> Now I am trying to add 1 more condition, like give me the total count of the
> values in col A only when the value is X in col B. Reject the count where the
> value <> X in col B.
>
> So as per above example total ABC count is 5 but in 1 row B col value is Y
> so it shud return the count of ABC as 4 in the first occurence of X (in row
> 2), same way total count of DEF is 7 value X is available in row 9 & 12 so it
> shud return the value as 2 in the 1st occurence where the value is X in col B
> (in row 9)
>
> I am extremely sorry to explian you in this manner, pls. let me know if u
> have any clarifications. Looking forward for your help. Thanks in Advance!!
>
|
Similar Threads
