Address function within countif function

[kashif.special2005]

Hi,

My Name is Kashif,

I am using Address function within countif function but it doesn't work

=COUNTIF(CONCATENATE(ADDRESS(2,1,1),":",ADDRESS(10,1,4)),ADDRESS(2,1,1))

Please help it's very urgent.

Thanks,
Kashif.

 

[kunalshrivastava.pune]

Try this Out and let me know.
=COUNTIF(INDIRECT(TEXT(CONCATENATE(ADDRESS(2,1,1),":",ADDRESS(10,1,4)),1)),INDIRECT(ADDRESS(2,1,1)))

 

[kashif.special2005]

Thanks a lot my friend......

 

[kashif.special2005]

Hi Kunal,

I want to create unique form given data.

data from A1=Company Name
B1=Lead Month

<TABLE style="WIDTH: 128pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=170 border=0><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: yellow" width=97 height=20>Company Name</TD><TD class=xl65 dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 55pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=73>Lead Month</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4Color Design</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4Color Design</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A technology</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A K Khan</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A K Khan</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20 Karma</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2bsure</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3 Dot</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>7 Hills</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Aamoda</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Accman</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ACPL</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Acro</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2bsure</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3 i business</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3 i business</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A O Sitams</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A O Sitams</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A O Sitams</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A O Sitams</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A O Sitams</TD><TD dir=ltr style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct'09</TD></TR></TBODY></TABLE>

You will see that 2bsure (Company) is available in aug'09 and Sep'09 but it is unique because it is in different month.

i am applying this formula in c2

=IF(B2=B1,COUNTIF(INDIRECT(TEXT(CONCATENATE(ADDRESS(2,1,1),":",ADDRESS(ROW(),1,4)),1)),INDIRECT(ADDRESS(ROW(),1,1))),COUNTIF(INDIRECT(TEXT(CONCATENATE(ADDRESS(ROW(),1,1),":",ADDRESS(ROW(),1,4)),1)),INDIRECT(ADDRESS(ROW(),1,1))))

that for unique if a company name appear in same month twice or more then this function should return 1 infront of that company name 2 if company appear in same month but when month change and this comapny is apeear once in this month then this function should return 1 because in this month this company is unique for this month.

that's why i am using address funtion in countif funtion because if month change then the addres should be change.

I hope you will be understand my problem.

Please help me to resolve this problem.

It's is very important.

Thanks,
Kashif.

 

[kunalshrivastava.pune]

Kasif, In C2 paste this formula.
=COUNTIF(INDIRECT(TEXT(CONCATENATE(ADDRESS(ROW(),1,1),":",ADDRESS(SUM(IF($B$2:$B$22=B2,1,0)),1,1)),1)),INDIRECT(ADDRESS(ROW(),1,1)))

And when ever the result is greater than 1 then this means duplicate records in same month.

 

[Man Lo]

Try this Out and let me know.
=COUNTIF(INDIRECT(TEXT(CONCATENATE(ADDRESS(2,1,1),":",ADDRESS(10,1,4)),1)),INDIRECT(ADDRESS(2,1,1)))

Thank you very much and you have help me to save a lot of effort...Thanks again.

 

[Aladin Akyurek]

Why do you need ADDRESS/INDIRECT here?

 

[Man Lo]

Why do you need ADDRESS/INDIRECT here?

Thanls for your concern. I always need to work on checking a database in different ways... it is just my work.