I was hoping that someone could tell me in the formula below used for detecting duplicates, what does the "SUMPRODUCT((A2:A7<>"")" do? I'm accustomed to seeing the SUMPRODUCT formula in a different format. Thank you in advance for your assistance.
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")),"No Duplicates","Duplicates")
Hello SJT,
In fact the complete SUMPRODUCT function (which incorporates COUNTIF) is
=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&""))
This is a reasonably well-documented formula (on various Excel forums at least) for counting distinct values (not including blanks). so if A2:A7 is
5
blank
5
2
4
blank
then the formula returns 3 because there are 3 different numbers. If this number is the same as the number of filled cells in the range (determined by COUNTA) then there are no duplicates.
Note: your formula will fail if A2:A7 includes any "formula blanks", i.e. "" returned by a formula, because COUNTA will count these and the SUMPRODUCT formula won't.
For an extensive explanation of
=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&""))
see the explanation here courtesy of Aladin Akyurek
http://www.mrexcel.com/board2/viewtopic.php?t=73502
Last edited by daddylonglegs; 06-15-2007 at 07:50 PM.
Thanks for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks