You don't compare RESULT of two, you check if the count of the number of times your equation is satisfying the criteria is greater than 1 or not. So if you equate $A$2:$A6=A6 you will get some TRUE and some FALSE. Similary for column B also you'll get some TRUE and FALSE. The multiplication of boolean values will either give 0 or 1. 1 only when both the equations give a TRUE. And you can only get 2 or more if there are duplicates. Otherwise it will always be 1.
Hope that explains ($A$2:$A2=A2)*($B$2:$B2=B2) Regards, Sam Mathai Chacko On Sat, Jan 28, 2012 at 7:21 PM, dguillett1 <dguille...@gmail.com> wrote: > Did you copy down the formula? > > Don Guillett > SalesAid Software > dguille...@gmail.com > > *From:* Amit Desai (MERU) <amit.de...@merucabs.com> > *Sent:* Saturday, January 28, 2012 2:43 AM > *To:* excel-macros@googlegroups.com > *Subject:* RE: $$Excel-Macros$$ Excel - Array Help > > > Thanks Noorain,**** > > **** > > I have just seen your email...**** > > **** > > I understand the formula...only part i am not getting is... this pat > ($A$2:$A2=A2)*($B$2:$B2=B2))>1...how the result of 2 comparison can be > greater than 1?**** > > **** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI > *Sent:* 21 January 2012 21:51 > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Excel - Array Help**** > > **** > > Dear Amit,**** > > > In this formula we are searching unique value of two columns > Red Color part is 1st criteria**** > > Green Color part is 2nd Criteria**** > > **** > > =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"Duplicate","Unique")**** > > we can also use..**** > > =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,"Duplicate","Unique")**** > > **** > > **** > > > -- **** > > Thanks & regards,**** > > Noorain Ansari**** > > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > **** > > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > **** > > On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) < > amit.de...@merucabs.com> wrote:**** > > Dear All,**** > > **** > > Can someone please explain how the below formula will work & I mean what > logic it will follow.**** > > **** > > Regards,**** > > *Amit Desai***** > > **** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI > *Sent:* 20 January 2012 16:22 > *To:* excel-macros@googlegroups.com **** > > > *Subject:* Re: $$Excel-Macros$$ Excel - Array Help**** > > **** > > Dear Sourabh, **** > > > > Please use this one to find unique value of more than one column without > using helping column > > *=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"Duplicate","Unique")***** > > See attached sheet. > On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra <rhtdmja...@gmail.com> > wrote:**** > > Thank you so much sir, if we want without help. col**** > > **** > > On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI <noorain.ans...@gmail.com> > wrote:**** > > Dear Sourabh, > > Please see attached sheet.**** > > > > -- **** > > Thanks & regards,**** > > Noorain Ansari**** > > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > **** > > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > **** > > **** > > On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra <rhtdmja...@gmail.com> > wrote:**** > > * > * > dear sir > plz tell me that how i can these fourmula on multiple match**** > > **** > > like**** > > attachment.**** > > **** > > On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI <noorain.ans...@gmail.com> > wrote:**** > > Dear Amar, > > Please use below formula to count total unique name > *=SUM(1/COUNTIF(B2:B6,B2:B6)) > *press ctrl+Shift+Enter > > or use this one to check unique or Duplicate Entry. > *=IF(COUNTIF($B$2:B2,B2)>1,"Duplicate","Unique") > * > see attached sheet.................. > > -- **** > > Thanks & regards,**** > > Noorain Ansari**** > > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > **** > > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > **** > > **** > > On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender < > kassi.re...@gmail.com> wrote:**** > > Hi Experts, > > I'm facing a problem in identifying duplicate names in a column. > > Here is what I'm looking for. > > If there are duplicates in column A i.e. > > A1 = Amar > A2= Vijay > A3 = Suresh > A4 = Ravi > A5 = Amar > > Then I would like to interior color A1 and A5 as they are duplicates, > I would like to use the cell references rather than Range's as in my > sheet Column is fixed but rows are not fixed. > > It'll be great if you can help me with this. > > Thanks in advance, > Amar > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > **** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > **** > > **** > > -- > mujhay dukh is baat ka nahin kay meri zaat ko > muntashir karny walay haath tairy thy > mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko > samaitnay walay haath tairy na thy > > > > -------------------------------------------------------------------------------- > > > Thanks & Regards > Sourabh > Contact Numbers: +91-94630-49202 > Website:http://adhurapyaar.co.cc**** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > **** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > **** > > **** > > -- > mujhay dukh is baat ka nahin kay meri zaat ko > muntashir karny walay haath tairy thy > mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko > samaitnay walay haath tairy na thy > > > > -------------------------------------------------------------------------------- > > > Thanks & Regards > Sourabh > Contact Numbers: +91-94630-49202 > Website:http://adhurapyaar.co.cc**** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > **** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > **** > ------------------------------ > > Disclaimer: This message and its attachments contain confidential > information and may also contain legally privileged information. This > message is intended solely for the named addressee. If you are not the > addressee indicated in this message (or authorized to receive for > addressee), you may not copy or deliver any part of this message or its > attachments to anyone or use any part of this message or its attachments. > Rather, you should permanently delete this message and its attachments (and > all copies) from your system and kindly notify the sender by reply e-mail. > Any content of this message and its attachments that does not relate to the > official business of Meru Cab Company Pvt. Ltd. must be taken not to have > been sent or endorsed by any of them. Email communications are not private > and no warranty is made that e-mail communications are timely, secure or > free from computer virus or other defect.**** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > > > > -- **** > > Thanks & regards,**** > > Noorain Ansari**** > > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > **** > > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > **** > > **** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com**** > > ------------------------------ > Disclaimer: This message and its attachments contain confidential > information and may also contain legally privileged information. This > message is intended solely for the named addressee. If you are not the > addressee indicated in this message (or authorized to receive for > addressee), you may not copy or deliver any part of this message or its > attachments to anyone or use any part of this message or its attachments. > Rather, you should permanently delete this message and its attachments (and > all copies) from your system and kindly notify the sender by reply e-mail. > Any content of this message and its attachments that does not relate to the > official business of Meru Cab Company Pvt. Ltd. must be taken not to have > been sent or endorsed by any of them. Email communications are not private > and no warranty is made that e-mail communications are timely, secure or > free from computer virus or other defect. > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > -- Sam Mathai Chacko -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
