Critical Counitif formula

[Christopher Naveen]

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!!

 

[Sheeloo]

=SUMPRODUCT(--(A1:A100="ABC"),--(B1:B100="X"))
will give you the count of rows with ABC in Col A and X in Col B

You can replace the values by reference to Cells like
=SUMPRODUCT(--(A1:A100=A1),--(B1:B100=B1))

 

[Shane Devenshire]

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

 

[Shane Devenshire]

Hi,

And here is the 2003 version of the second formula:

=IF(SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2="x"))=1,IF(B2="x",SUMPRODUCT(--(A2:A$13=A2),--(B2:$B$13="x")),""),"")