Updating SUMPRODUCT Formula?

  • Thread starter Thread starter dkenebre
  • Start date Start date
D

dkenebre

Currently, I used the following formula to display how many digits from
the previous 3 cells were repeated in the next 3 cells but I in some
examples it does not give me the answer I am looking for see examples
below:

Formula Used: =SUMPRODUCT(COUNTIF(AF575:AH575,AF577:AH577))

Correct Sample #1
AF575:AH575 3,7,0
AF577:AH577 3,5,9
C577=1 correct

Incorrect Sample #2
AF575:AH575 3,3,3
AF577:AH577 3,5,9
C577=3, only 1 digit is actually repeated, therefore the answer should
be 1

Incorrect Sample #3
AF575:AH575 1,6,3
AF577:AH577 3,3,3
C577=3, only 1 digit is actually repeated, therefore the answer should
be 1

Incorrect Sample #4
AF575:AH575 4,5,5
AF577:AH577 4,5,9
C577=3, only 2 digit is actually repeated, therefore the answer should
be 2

Can someone help me update this formula so that all 4 sample are
correct. Thank you.
 
You are totaling the number of matches found, instead of counting the
number of positions where matches were found

=SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1)

will fix samples 2 & 4, but still fails with 3, because you need a
unique reference line, yet in your examples either row can contain
repeated values. Note that the ()*1 is needed to convert the boolean
values to numbers so that they can be summed.

=MIN( SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1),
SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1) )

will handle all four of your samples, but will return 3 instead of 2 for

AF575:AH575 4,5,5
AF575:AH577 4,5,5

If you will not have a reference line of unique values for comparison
(i.e. if it is possible that each lines may simultaneously have less
than 3 unique values), then I think you will have to create one in a
helper range before you can have a general solution without writing a
VBA function.

Laurent Longre's MoreFunc.xll from
http://longre.free.fr/downloads/Morefunc.zip
contains an array function called UNIQUEVALUES() that will return a
vertical array of unique values from an input array. The size of the
vertical array will be the number cells in the input array, with
trailing null strings as place holders for repeated values. Assuming
that there will be no null strings in either AF575:AH575 or AF577:AH577
then you can array enter (Ctrl-Shift-Enter) =UNIQUEVALUES(AF577:AH577)
in three rows or =TRANSPOSE(UNIQUEVALUES(AF577:AH577)) in three columns.
For concreteness, assume the latter form is in BF577:BH577 then

=SUMPRODUCT((COUNTIF(AF575:AH575,BF577:BH577)>0)*1)

should give what you want.
 
Thanks Jerry for the help. Can we design a VBA function that would work
in all 4 conditions including the following 2 samples:

4,5,9
5,9,4
=3
or

3,3,3
3,3,3
=1

Each of the 3 ells will only have 1 digit, between 0-9. I plan to add a
git in each AF:AH cell throughout the sheet that have orange colored
cells. How would you design this one?
 
Here is an "hammer and tong" approach with no error checking. It should
get you started.

Function UniqueMatches(row1 As Range, row2 As Range) As Integer
Dim n1 As Integer, i As Integer, j As Integer, matches As Integer
Dim x As Double, cel As Variant, match As Boolean
matches = 0
n1 = row1.Count
For i = 1 To n1
x = row1(1, i).Value
x = row1(1, i).Value ' get value
For j = 1 To i - 1 ' see if value already processed
If x = row1(1, j).Value Then j = n1 + 1
Next j
If j = i Then ' check row2 only if not processed
match = False
For Each cel In row2
If x = cel.Value Then match = True
Next
If match Then matches = matches + 1
End If
Next i
UniqueMatches = matches
End Function

Jerry
 
=SUM(--ISNUMBER(MATCH(IF(UNIQUEVALUES(AF575:AH575)<>"",UNIQUEVALUES(AF575:AH
575)),UNIQUEVALUES(AF577:AH577),0)))

Better:

=SUM(--ISNUMBER(MATCH(IF(SETV(UNIQUEVALUES(AF575:AH575))<>"",GETV()),UNIQUEV
ALUES(AF577:AH577),0)))

which must be confirmed with control+shift enter.

Jerry W. Lewis said:
You are totaling the number of matches found, instead of counting the
number of positions where matches were found

=SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1)

will fix samples 2 & 4, but still fails with 3, because you need a
unique reference line, yet in your examples either row can contain
repeated values. Note that the ()*1 is needed to convert the boolean
values to numbers so that they can be summed.

=MIN( SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1),
SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)*1) )

will handle all four of your samples, but will return 3 instead of 2 for

AF575:AH575 4,5,5
AF575:AH577 4,5,5

If you will not have a reference line of unique values for comparison
(i.e. if it is possible that each lines may simultaneously have less
than 3 unique values), then I think you will have to create one in a
helper range before you can have a general solution without writing a
VBA function.

Laurent Longre's MoreFunc.xll from
http://longre.free.fr/downloads/Morefunc.zip
contains an array function called UNIQUEVALUES() that will return a
vertical array of unique values from an input array. The size of the
vertical array will be the number cells in the input array, with
trailing null strings as place holders for repeated values. Assuming
that there will be no null strings in either AF575:AH575 or AF577:AH577
then you can array enter (Ctrl-Shift-Enter) =UNIQUEVALUES(AF577:AH577)
in three rows or =TRANSPOSE(UNIQUEVALUES(AF577:AH577)) in three columns.
For concreteness, assume the latter form is in BF577:BH577 then

=SUMPRODUCT((COUNTIF(AF575:AH575,BF577:BH577)>0)*1)

should give what you want.
Click to expand...
 
dkenebre > said:
AF575:AH575 3,7,0
AF577:AH577 3,5,9
. . . 1 correct ....
AF575:AH575 3,3,3
AF577:AH577 3,5,9
. . . answer should be 1 ....
AF575:AH575 1,6,3
AF577:AH577 3,3,3
. . . answer should be 1 ....
AF575:AH575 4,5,5
AF577:AH577 4,5,9
. . . answer should be 2
Click to expand...

=SUMPRODUCT(--ISNUMBER(MATCH(AF577:AH577,AF575:AH575,0))
/COUNTIF(AF577:AH577,AF577:AH577))
 
...
...
=SUMPRODUCT(--ISNUMBER(MATCH(AF577:AH577,AF575:AH575,0))
/COUNTIF(AF577:AH577,AF577:AH577))
Click to expand...

Too many function calls. Make that

=SUMPRODUCT((COUNTIF(AF575:AH575,AF577:AH577)>0)
/COUNTIF(AF577:AH577,AF577:AH577))
 
Jerry Thanks, your function works fine.
Aladin thank you also, your formula works.
Harlan Thanks for the concise formula, it works also.
I do appreciate the help
 
Back
Top