Mother of a sumproduct (ish) problem!

  • Thread starter Thread starter Wild Bill
  • Start date Start date
W

Wild Bill

At least it is to me - now I humbly beseech your magic :)
A1 to A10 contain 10 unique letters, e.g. A,Q,E...
B1 to B10 contain 10 unique letters, e.g. Z,B,A...
C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q...
D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B...
E1 to E100 contain values, e.g. 9,1,3,5,7,2...

In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in
Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy
is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for
the sample data above. Thus for what I presented, the C:D matches to
A:B give the solution 2 + 9 + 5.

Of course this could be done systematically in VBA, but to do in Excel I
can't escape adding a helper column F1:F10 and summing that. I've tried
all sorts of SUM() and SUMPRODUCT and ISNUMBER(MATCH) and coercion and
array formula entry and it just isn't happening for me. I've read scores
of Aladin solutions and can't find one to fit this exactly.

I see it as a 3 term SUMPRODUCT or SUM(x*y*z) or SUM(IF...; with a
simple 3rd term ",E$1:E$100)" . --(ISNUMBER(MATCH seemed most promising
and I tried to run through every permutation. Just for the A and C part
(B and D are analogous) I've homed in on (A1:A$10,C$1:C$100), adding $
in A1, reversing the A and C order, removing the A$10 ... it laughs at
everything I throw at it. Please make mincemeat of this troublesome
beast :( :(
 
If sample data from A1 thru E100 is:

E A N J 1
B W M A 2
L H S H 3
Y C J J 4
H I N J 5
N V E G 6
W O D R 7
I L K R 8
T U A S 9
J B S U 10
N I 11
A C 12
C Q 13
J B 14
O N 15
I L 16
W Y 17
O A 18
J E 19
N P 20
G N 21
H L 22
F N 23
I C 24
N G 25
V J 26
N T 27
A C 28
Q C 29
W U 30
O N 31
G U 32
N J 33
M X 34
R B 35
L F 36
L O 37
S H 38
F B 39
F P 40
C P 41
F T 42
X M 43
N E 44
R Q 45
P N 46
F O 47
O A 48
Z Q 49
O F 50
D W 51
Z E 52
R T 53
H D 54
P F 55
T N 56
K I 57
T U 58
Y L 59
W B 60
O G 61
P U 62
P W 63
N N 64
R K 65
P G 66
Q N 67
X L 68
Z U 69
U Y 70
T E 71
M Y 72
W U 73
H F 74
R R 75
P J 76
B T 77
W X 78
U D 79
B F 80
A A 81
W D 82
Q G 83
V O 84
P E 85
N W 86
G U 87
M M 88
P X 89
Z K 90
R F 91
N K 92
G H 93
V F 94
L M 95
P X 96
O M 97
Q X 98
J Z 99
A Y 100
then the following:

=SUMPRODUCT(((--(C$1:C$100=A1)*(--(D$1:D$100=B1))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A2)*(--(D$1:D$100=B2))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A3)*(--(D$1:D$100=B3))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A4)*(--(D$1:D$100=B4))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A5)*(--(D$1:D$100=B5))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A6)*(--(D$1:D$100=B6))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A7)*(--(D$1:D$100=B7))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A8)*(--(D$1:D$100=B8))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A9)*(--(D$1:D$100=B9))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A10)*(--(D$1:D$100=B10))*(E$1:E$100))))

yield:

0
0
0
0
0
0
0
16
58
14

--
Gary''s Student - gsnu200746


Wild Bill said:
At least it is to me - now I humbly beseech your magic :)
A1 to A10 contain 10 unique letters, e.g. A,Q,E...
B1 to B10 contain 10 unique letters, e.g. Z,B,A...
C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q...
D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B...
E1 to E100 contain values, e.g. 9,1,3,5,7,2...

In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in
Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy
is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for
the sample data above. Thus for what I presented, the C:D matches to
A:B give the solution 2 + 9 + 5.

Of course this could be done systematically in VBA, but to do in Excel I
can't escape adding a helper column F1:F10 and summing that. I've tried
all sorts of SUM() and SUMPRODUCT and ISNUMBER(MATCH) and coercion and
array formula entry and it just isn't happening for me. I've read scores
of Aladin solutions and can't find one to fit this exactly.

I see it as a 3 term SUMPRODUCT or SUM(x*y*z) or SUM(IF...; with a
simple 3rd term ",E$1:E$100)" . --(ISNUMBER(MATCH seemed most promising
and I tried to run through every permutation. Just for the A and C part
(B and D are analogous) I've homed in on (A1:A$10,C$1:C$100), adding $
in A1, reversing the A and C order, removing the A$10 ... it laughs at
everything I throw at it. Please make mincemeat of this troublesome
beast :( :(
Click to expand...
 
Thank you, but that's the helper column answer <g> (I came to the same
solution as such, though, so thanks for confirming). It's finally
solved now, without a helper: what I hadn't realized is that you can get
away with multiple cell concatenation in sumproduct(match). Perhaps that
was too mundane for you guys to even mention - but it was torture to me
until that breakthrough - hours - arggggh.

=SUMPRODUCT(--ISNUMBER(MATCH(C$1:C$100&D$1:D$100,A$1:A$10&B$1:B$10,0)),E$1:E$100)

Note that if you reverse the MATCH terms it fails; its first term must
be the nonunique pair (C&D).

=SUMPRODUCT(((--(C$1:C$100=A1)*(--(D$1:D$100=B1))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A2)*(--(D$1:D$100=B2))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A3)*(--(D$1:D$100=B3))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A4)*(--(D$1:D$100=B4))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A5)*(--(D$1:D$100=B5))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A6)*(--(D$1:D$100=B6))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A7)*(--(D$1:D$100=B7))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A8)*(--(D$1:D$100=B8))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A9)*(--(D$1:D$100=B9))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A10)*(--(D$1:D$100=B10))*(E$1:E$100))))
At least it is to me - now I humbly beseech your magic :)
A1 to A10 contain 10 unique letters, e.g. A,Q,E...
B1 to B10 contain 10 unique letters, e.g. Z,B,A...
C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q...
D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B...
E1 to E100 contain values, e.g. 9,1,3,5,7,2...

In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in
Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy
is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for
the sample data above. Thus for what I presented, the C:D matches to
A:B give the solution 2 + 9 + 5.

Of course this could be done systematically in VBA, but to do in Excel I
can't escape adding a helper column F1:F10 and summing that. I've tried
all sorts of SUM() and SUMPRODUCT and ISNUMBER(MATCH) and coercion and
array formula entry and it just isn't happening for me. I've read scores
of Aladin solutions and can't find one to fit this exactly.

I see it as a 3 term SUMPRODUCT or SUM(x*y*z) or SUM(IF...; with a
simple 3rd term ",E$1:E$100)" . --(ISNUMBER(MATCH seemed most promising
and I tried to run through every permutation. Just for the A and C part
(B and D are analogous) I've homed in on (A1:A$10,C$1:C$100), adding $
in A1, reversing the A and C order, removing the A$10 ... it laughs at
everything I throw at it. Please make mincemeat of this troublesome
beast :( :(
Click to expand...
Click to expand...
Click to expand...
 
I am glad you found a solution you can use. For me, it was a good way to
practice using SUMPRODUCT.
--
Gary''s Student - gsnu200746


Wild Bill said:
Thank you, but that's the helper column answer <g> (I came to the same
solution as such, though, so thanks for confirming). It's finally
solved now, without a helper: what I hadn't realized is that you can get
away with multiple cell concatenation in sumproduct(match). Perhaps that
was too mundane for you guys to even mention - but it was torture to me
until that breakthrough - hours - arggggh.

=SUMPRODUCT(--ISNUMBER(MATCH(C$1:C$100&D$1:D$100,A$1:A$10&B$1:B$10,0)),E$1:E$100)

Note that if you reverse the MATCH terms it fails; its first term must
be the nonunique pair (C&D).

=SUMPRODUCT(((--(C$1:C$100=A1)*(--(D$1:D$100=B1))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A2)*(--(D$1:D$100=B2))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A3)*(--(D$1:D$100=B3))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A4)*(--(D$1:D$100=B4))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A5)*(--(D$1:D$100=B5))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A6)*(--(D$1:D$100=B6))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A7)*(--(D$1:D$100=B7))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A8)*(--(D$1:D$100=B8))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A9)*(--(D$1:D$100=B9))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A10)*(--(D$1:D$100=B10))*(E$1:E$100))))
At least it is to me - now I humbly beseech your magic :)
A1 to A10 contain 10 unique letters, e.g. A,Q,E...
B1 to B10 contain 10 unique letters, e.g. Z,B,A...
C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q...
D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B...
E1 to E100 contain values, e.g. 9,1,3,5,7,2...

In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in
Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy
is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for
the sample data above. Thus for what I presented, the C:D matches to
A:B give the solution 2 + 9 + 5.

Of course this could be done systematically in VBA, but to do in Excel I
can't escape adding a helper column F1:F10 and summing that. I've tried
all sorts of SUM() and SUMPRODUCT and ISNUMBER(MATCH) and coercion and
array formula entry and it just isn't happening for me. I've read scores
of Aladin solutions and can't find one to fit this exactly.

I see it as a 3 term SUMPRODUCT or SUM(x*y*z) or SUM(IF...; with a
simple 3rd term ",E$1:E$100)" . --(ISNUMBER(MATCH seemed most promising
and I tried to run through every permutation. Just for the A and C part
(B and D are analogous) I've homed in on (A1:A$10,C$1:C$100), adding $
in A1, reversing the A and C order, removing the A$10 ... it laughs at
everything I throw at it. Please make mincemeat of this troublesome
beast :( :(
Click to expand...
Click to expand...
Click to expand...
 
I hope I didn't imply ungratefulness as it was clear that you put in
time and effort (and answered correctly, always a bonus LOL), and even
that someone bothered to reply at all was appreciated. I just lucked out
this time and shared the result. I was also hoping you felt just as you
say below: that the process of answering a question, whether OP reads or
uses or even acknowledges your effort, often pays off in reinforcing or
extending the solver's own skill in some aspect. That's how the system
ought to work :)

I humbly extend that gratitude to a significant number of you in the NGs
- and stand gaping at how some of you manage it so prodigiously. I used
to think God only allocated us one Ogilvy - but there are a lot of you.
 
There is no need for the unary minuses when you multiply the arrays, if you
use them you can use the built in format of SUMPRODUCT using commas between
the arrays


sumproduct(--(range1=x),--(range2=y),range3)

and the benefit vis-à-vis the multiplied arrays besides being slightly
faster is that it ignores text in the sum range whereas multiplying causes
value errors


--

Regards,

Peo Sjoblom

Gary''s Student said:
I am glad you found a solution you can use. For me, it was a good way to
practice using SUMPRODUCT.
--
Gary''s Student - gsnu200746


Wild Bill said:
Thank you, but that's the helper column answer <g> (I came to the same
solution as such, though, so thanks for confirming). It's finally
solved now, without a helper: what I hadn't realized is that you can get
away with multiple cell concatenation in sumproduct(match). Perhaps that
was too mundane for you guys to even mention - but it was torture to me
until that breakthrough - hours - arggggh.

=SUMPRODUCT(--ISNUMBER(MATCH(C$1:C$100&D$1:D$100,A$1:A$10&B$1:B$10,0)),E$1:E$100)

Note that if you reverse the MATCH terms it fails; its first term must
be the nonunique pair (C&D).

=SUMPRODUCT(((--(C$1:C$100=A1)*(--(D$1:D$100=B1))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A2)*(--(D$1:D$100=B2))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A3)*(--(D$1:D$100=B3))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A4)*(--(D$1:D$100=B4))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A5)*(--(D$1:D$100=B5))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A6)*(--(D$1:D$100=B6))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A7)*(--(D$1:D$100=B7))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A8)*(--(D$1:D$100=B8))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A9)*(--(D$1:D$100=B9))*(E$1:E$100))))
=SUMPRODUCT(((--(C$1:C$100=A10)*(--(D$1:D$100=B10))*(E$1:E$100))))
Click to expand...
At least it is to me - now I humbly beseech your magic :)
A1 to A10 contain 10 unique letters, e.g. A,Q,E...
B1 to B10 contain 10 unique letters, e.g. Z,B,A...
C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q...
D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B...
E1 to E100 contain values, e.g. 9,1,3,5,7,2...

In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in
Cy:Dy and sum E when matched. So I want to sum Ey for the rows where
[Cy
is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc.
for
the sample data above. Thus for what I presented, the C:D matches to
A:B give the solution 2 + 9 + 5.

Of course this could be done systematically in VBA, but to do in Excel
I
can't escape adding a helper column F1:F10 and summing that. I've
tried
all sorts of SUM() and SUMPRODUCT and ISNUMBER(MATCH) and coercion and
array formula entry and it just isn't happening for me. I've read
scores
of Aladin solutions and can't find one to fit this exactly.

I see it as a 3 term SUMPRODUCT or SUM(x*y*z) or SUM(IF...; with a
simple 3rd term ",E$1:E$100)" . --(ISNUMBER(MATCH seemed most
promising
and I tried to run through every permutation. Just for the A and C
part
(B and D are analogous) I've homed in on (A1:A$10,C$1:C$100), adding $
in A1, reversing the A and C order, removing the A$10 ... it laughs at
everything I throw at it. Please make mincemeat of this troublesome
beast :( :(
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much - useful, instructive, great tip. I understand that
you're only referring to nonBoolean multiplication, but just for clarity
to others who may overextend your comment to the rest of the thread,
=SUMPRODUCT(--ISNUMBER... still requires -- to "numeralize" the Boolean.
 
My post was directed to Gary's student's formula where he used the unary
minuses and multiplication
 
Back
Top