Critical Counitif formula

C

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

S

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

S

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

S

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")),""),"")