Adding columns to make a specified value.

I have 5 columns, 3 with 22 entries and two with 11 entries. The entries are numbers between 1 and 24. I have to work out all of the combinations that would include 5 numbers, one from each column, that add up to 45. How can I do this?

[paludgnp]

It is important to know if the values in the columns without entries are equal to 0 and can be counted as:
C1 C2 C3 C4 C5
11 11 11 12 0

C1+C2+C3+C4+C5=45
Is this acceptable or not?

I yes then, and if you have 22 entries in your 3 first columns, and 11 in your 2 last, then code follows...

(this code is correct if you match 45 exactly, other wise change the following if 'sum = 45 then count= count+1' for if sum <= 45 then count= count+1 )

sub Sum45()
dim i as byte
dim j as byte
dim k as byte
dim l as byte
dim m as byte
dim sum as byte
dim Count as integer

count=0
For i=1 to i=1+22
sum=Val(cells(i,1))
for j=1 to j=1+22
sum=Val(cells(i,1))+Val(cells(j,2))
if sum =< 45 then
for k=1 to K=1+22
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))
if sum<45 then
for l=1 to l=1+11
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))
if sum<45 then
for m=1 to m=1+11
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))+Val(cells(m,5))
if sum = 45 then count= count+1
next m
end if
next l
end if
nexr k
end if
next j
next i
end sub

I hope I could help you,
Pierre

On 2002-02-21 05:41, paludgnp wrote:
It is important to know if the values in the columns without entries are equal to 0 and can be counted as:
C1 C2 C3 C4 C5
11 11 11 12 0

C1+C2+C3+C4+C5=45
Is this acceptable or not?

I yes then, and if you have 22 entries in your 3 first columns, and 11 in your 2 last, then code follows...

(this code is correct if you match 45 exactly, other wise change the following if 'sum = 45 then count= count+1' for if sum <= 45 then count= count+1 )

sub Sum45()
dim i as byte
dim j as byte
dim k as byte
dim l as byte
dim m as byte
dim sum as byte
dim Count as integer

count=0
For i=1 to i=1+22
sum=Val(cells(i,1))
for j=1 to j=1+22
sum=Val(cells(i,1))+Val(cells(j,2))
if sum =< 45 then
for k=1 to K=1+22
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))
if sum<45 then
for l=1 to l=1+11
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))
if sum<45 then
for m=1 to m=1+11
sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))+Val(cells(m,5))
if sum = 45 then count= count+1
next m
end if
next l
end if
nexr k
end if
next j
next i
end sub

I hope I could help you,
Pierre

Thanks for your reply, blank cells are at 0 so I think this will work. Can you send a formula, and what cell(s) should it go in?