counts repeated number in two different area only once

[sierra spiegel]

I have a formula that count repeat number only once, but in the same area only:

IF(COUNTIF(A2:C3,A1)>0,1,0)+IF(COUNTIF(A6:C9)>0,1,0)

I want to count all the repeats to A1 in both area only once.

thanks


EggHeadCafe - Software Developer Portal of Choice
MDI Inter-Form Communication with Events
http://www.eggheadcafe.com/tutorial...5f-20ce4f44f644/mdi-interform-communicat.aspx

 

[ryguy7272]

That compound function doesn't work. Maybe this is what you want:
=IF(COUNTIF(A2:C3,A1)>0,1,0)+IF(COUNTIF(A6:C9,A1)>0,1,0)

Or, this:
=IF(COUNTIF(A2:C3,A1)>0,1,0)+IF(COUNTIF(A6:C9,A1)>1,1,0)

HTH,
Ryan---

 

[sierra spiegel]

Thank you very much, Ryan. Your formula works perfectly.





ryguy7272 wrote:

That compound function does not work.
02-Nov-09

That compound function does not work. Maybe this is what you want
=IF(COUNTIF(A2:C3,A1)>0,1,0)+IF(COUNTIF(A6:C9,A1)>0,1,0

Or, this
=IF(COUNTIF(A2:C3,A1)>0,1,0)+IF(COUNTIF(A6:C9,A1)>1,1,0

HTH
Ryan--

-
Ryan--
If this information was helpful, please indicate this by clicking ''Yes''

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Membership and Roles in Silverlight 3
http://www.eggheadcafe.com/tutorial...62-48ad5c7f70b4/aspnet-membership-and-ro.aspx