If I understand it right, I cannot do this:

={SUM(VLOOKUP(A1:A4,Sheet2!$A$1:$B$8,2,FALSE))}

To get the sum of points in this example

Sheet 1

A B etc...
1 1
2 1
3 1
4 1
5 this is where I put the formula hoping to get 40 (10 + 10 + 10 +
10)

Sheet 2

A B C etc...
1 1 10
2 2 8
3 3 6
4 4 4
5 5 3
6 6 2
7 7 1
8 8 0

If I used the formula =VLOOKUP(A1,Sheet2!$A$1:$B$8,2,FALSE) in Sheet1,
B1, I would get 10 as expected. I could then fill B2:B4 and sum the
values, but I want the total in one cell. There must be a way to do it.

I saw a solution posted here
(http://groups.google.com/group/micro...ksheet.functio
ns/browse_thread/thread/af784678f7b56f38) that works - but not for
repeated values. Can someone point me in the right direction?

Thanks,
Andrew


--
Posted via a free Usenet account from http://www.teranews.com

"Ragdyer" wrote in
:

=SUMPRODUCT((Sheet2!A1:A8=N(INDIRECT({"A1","A2","A 3","A4"})))*Sheet2!B1
:B8)

Thanks! That did the trick.

Why can't I use CELL("address",A1:A4) instead of the array constant?

--
Posted via a free Usenet account from http://www.teranews.com

Try this in A5 of Sheet1:

=SUMPRODUCT((Sheet2!A1:A8=N(INDIRECT({"A1","A2","A 3","A4"})))*Sheet2!B1:B8)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Andrew" wrote in message
...
If I understand it right, I cannot do this:

={SUM(VLOOKUP(A1:A4,Sheet2!$A$1:$B$8,2,FALSE))}

To get the sum of points in this example

Sheet 1

A B etc...
1 1
2 1
3 1
4 1
5 this is where I put the formula hoping to get 40 (10 + 10 + 10 +
10)

Sheet 2

A B C etc...
1 1 10
2 2 8
3 3 6
4 4 4
5 5 3
6 6 2
7 7 1
8 8 0

If I used the formula =VLOOKUP(A1,Sheet2!$A$1:$B$8,2,FALSE) in Sheet1,
B1, I would get 10 as expected. I could then fill B2:B4 and sum the
values, but I want the total in one cell. There must be a way to do it.

I saw a solution posted here
(http://groups.google.com/group/micro...ksheet.functio
ns/browse_thread/thread/af784678f7b56f38) that works - but not for
repeated values. Can someone point me in the right direction?

Thanks,
Andrew


--
Posted via a free Usenet account from http://www.teranews.com

Why can't I use CELL("address",A1:A4) instead of the array constant?

Because CELL doesn't work on arrays.

Another one:

=SUMPRODUCT(COUNTIF(A1:A4,Sheet2!A1:A8),Sheet2!B1: B8)


--
Biff
Microsoft Excel MVP


"Andrew Clark" wrote in message
...
"Ragdyer" wrote in
:

=SUMPRODUCT((Sheet2!A1:A8=N(INDIRECT({"A1","A2","A 3","A4"})))*Sheet2!B1
:B8)

Thanks! That did the trick.

Why can't I use CELL("address",A1:A4) instead of the array constant?

--
Posted via a free Usenet account from http://www.teranews.com

Nice ... and uncomplicated!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
Why can't I use CELL("address",A1:A4) instead of the array constant?

Because CELL doesn't work on arrays.

Another one:

=SUMPRODUCT(COUNTIF(A1:A4,Sheet2!A1:A8),Sheet2!B1: B8)


--
Biff
Microsoft Excel MVP


"Andrew Clark" wrote in message
...
"Ragdyer" wrote in
:

=SUMPRODUCT((Sheet2!A1:A8=N(INDIRECT({"A1","A2","A 3","A4"})))*Sheet2!B1
:B8)

Thanks! That did the trick.

Why can't I use CELL("address",A1:A4) instead of the array constant?

--
Posted via a free Usenet account from http://www.teranews.com