COUNTIF

[santhoshkunder]

Please help me out on this


Here is the data


1 column has sales order no and other column has line number,say for example,

clumnn A Column B column C

2500 1 0

3000 1 1

2500 2 0

2500 3 3


Now the formula should be in such a way that the value 2500 has 3 line number i e 1,2,3.but i would like to have the "max" of these 3 number i,e 3 and rest other value should return as 0 as shown in the column c.

 

[pradhishnair]

the data in your column b could be generated using

{=+SUM(IF($A$1:A1=A1,1,0))}. OR

{=+SUM(--(($A$1:A1)=A1))}

please note that both above formulae are array formula. OR

Also another option u may achieve this is

=+SUMPRODUCT(--(($A$1:A1)=A1))

and drag the formula in the subsequent cells

i am not sure what data u intend to generate in column C. could you be more specific please.

 

[NARAYANK991]

Hi Santosh ,


I am assuming that data in columns A and B will be manually entered , and only the values in column C need to be generated by formulae. If this is so , then in column C , starting with cell C2 , put in the following formula :


=IF(B2<COUNTIF($A$2:$A$5,A2),0,B2)


If you want that the values in column B too should come from formulae , then , in addition to what Pradish has already posted , the following formula also can be used :


=COUNTIF($A$2:A2,A2)


Narayan

 

[SirJB7]

Hi, santhoshkunder!

Let's change what NARAYANK991 posted to:

B2: =CONTAR.SI(A$2:A2;A2) -----> in english: =COUNTIF(A$2:A2,A2)

C2: =SI(B2=CONTAR.SI(A$2:A$5;A2);B2;0) -----> in english: =IF(B2=COUNTIF(A$2:A$5,A2),B2,0)

Then copy down to B3:B5.

I think that now it does the job.

Try and let us know.

Regards!