Counting Duplicates
Hello!
I would like to know how I can use COUNTIF/COUNTIFS to count duplicates in two columns in Excel 2010. The two columns do not have the exact same number of rows. There are no duplicates within column A or within column B, but I want to know if there are duplicates across the two columns. The ranges are A2:A12868 and C2:C19826. I have tried using =COUNTIF(A2:A12868,C2:C19826), but this is incorrect. Thank you for your help!
Countif with two nested ranges will return an array. Instead, encapsulate the expression with SUMPRODUCT to add up the results of the array.
Please Login or Register to view this content.
Alternatively, you can coerce the array into a sum by using SUM and entering the formula as an ARRAY FORMULA.
But this requires you to confirm the formula with Ctrl+Shift+Enter.Please Login or Register to view this content.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
=SUM(INDEX(COUNTIF(A2:A12868,C2:C19826),0))
OR
BELOW ARRAY FORMULA
=SUM(COUNTIF(A2:A12868,C2:C19826)) Confirm with Shift+Ctrl+Enter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks