Sum / Lookup
 

I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

One way
=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D 2,J2:J4=E2,J2:J4=F2))*K2:K4)



On Dec 12, 1:48*am, "Mal" wrote:
I have a row of data (only six items).

A * *B * *C * *D * *E * *F
1 * * 3 * * 9 * *4 * * 2 * *8

A table of two columns
J * * * *K
1 * * * *3
2 * * * *2
3 * * * *1

=4 * * 0

I want to look up each of the values in A to F, assign a *value from column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

another
=SUMPRODUCT(--(ISNUMBER(MATCH(J2:J4,A2:F2,0))*K2:K4))

On Dec 12, 7:30*am, Don Guillett wrote:
One way
=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D 2,J2:J4=E2,J2:J4=F2))*K2: K4)

On Dec 12, 1:48*am, "Mal" wrote:







I have a row of data (only six items).

A * *B * *C * *D * *E * *F
1 * * 3 * * 9 * *4 * * 2 * *8

A table of two columns
J * * * *K
1 * * * *3
2 * * * *2
3 * * * *1

=4 * * 0

I want to look up each of the values in A to F, assign a *value from column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal



"Mal" wrote in message
d.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.



Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal



"Mal" wrote in message
nd.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal




"Gord Dibben" wrote in message
...
Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.



Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal



"Mal" wrote in message
ond.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

On Mon, 12 Dec 2011 17:48:04 +1000, "Mal" wrote:

I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal


Assuming that your A:F data is in Row 2; and that your J:K data starts in Row 1

Change J4 from =4 to just the number 4

Then enter this formula in G2:

=SUMPRODUCT(N(OFFSET(K1,MATCH(A2:F2,J1:J4)-1,0)))

Sum / Lookup
 

Thanks Ron.
I believe you have nailed it.
All help appreciated.

Mal

"Ron Rosenfeld" wrote in message
...
On Mon, 12 Dec 2011 17:48:04 +1000, "Mal" wrote:

I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal


Assuming that your A:F data is in Row 2; and that your J:K data starts in
Row 1

Change J4 from =4 to just the number 4

Then enter this formula in G2:

=SUMPRODUCT(N(OFFSET(K1,MATCH(A2:F2,J1:J4)-1,0)))

Sum / Lookup
 

On Tue, 13 Dec 2011 12:51:55 +1000, "Mal" wrote:

Thanks Ron.
I believe you have nailed it.
All help appreciated.

Mal


Glad to help. Thanks for the feedback.

Sum / Lookup
 

Apologies Mal.

Did not fully test..............left out row 4 data in J and K so was
not your described layout as I stated.

I see Ron has you set up.


Gord

On Tue, 13 Dec 2011 10:52:12 +1000, "Mal"
wrote:

Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal




"Gord Dibben" wrote in message
.. .
Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.



Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal



"Mal" wrote in message
pond.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

Sum / Lookup
 

On Dec 12, 6:52*pm, "Mal" wrote:
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal

"Gord Dibben" wrote in message

...







Please explain what "doesn't work" means.

Errors? * * * * * *Nothing? * * * * *Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.

Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal

"Mal" wrote in message
ond.com...
I have a row of data (only six items).

A * *B * *C * *D * *E * *F
1 * * 3 * * 9 * *4 * * 2 * *8

A table of two columns
J * * * *K
1 * * * *3
2 * * * *2
3 * * * *1
=4 * * 0

I want to look up each of the values in A to F, assign a *value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

I just re-tested and find that, using the second formula, if you
change c2 to 9 you still get 6 because your numbers still have
1,2,3......Try changing e2

Sum / Lookup
 

Don,
I think you misread what I said. I said if you change c2 from 9 to 1 the
answer should be 9.
The data in A2 to F2 is variable so if for example each of the six cells (a2
to f2) had the figure "1" in them, we apply a value of "3" to each from the
table J2:K4) and sum 6 times 3 = 18.
If you put "1" into each of cells a2:f2 I think you will see the result
comes up as 3 when it should be 18. I can not see why this is so.
However I am happy as Ron as given me a solution.
Thanks for your help.

Mal
"Don Guillett" wrote in message
...
On Dec 12, 6:52 pm, "Mal" wrote:
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the
result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal

"Gord Dibben" wrote in message

...







Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.

Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal

"Mal" wrote in message
ond.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

I just re-tested and find that, using the second formula, if you
change c2 to 9 you still get 6 because your numbers still have
1,2,3......Try changing e2