Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 128
Default Show result of a SUM in a VLOOKUP

I need to use a vlookup and sum function (I think) in an Excel 2000 workbook
with two worksheets. The Update worksheet shows client ref in column A, then
along each row, shows various monetary values of business done by each
client. The Summary worksheet also shows the client ref in column A, but I
need this worksheet to sum each of the values entered on a row, but pull out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Show result of a SUM in a VLOOKUP

Try this:

A2 = client

=SUMPRODUCT((Update!A2:A10=A2)*Update!B2:F10)

Biff

"Sarah (OGI)" wrote in message
...
I need to use a vlookup and sum function (I think) in an Excel 2000
workbook
with two worksheets. The Update worksheet shows client ref in column A,
then
along each row, shows various monetary values of business done by each
client. The Summary worksheet also shows the client ref in column A, but
I
need this worksheet to sum each of the values entered on a row, but pull
out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 128
Default Show result of a SUM in a VLOOKUP

Thank you, but that doesn't seem to work.
What is the 'A2:A10=A2' part of the formula doing? Is it working as a
vlookup?

"T. Valko" wrote:

Try this:

A2 = client

=SUMPRODUCT((Update!A2:A10=A2)*Update!B2:F10)

Biff

"Sarah (OGI)" wrote in message
...
I need to use a vlookup and sum function (I think) in an Excel 2000
workbook
with two worksheets. The Update worksheet shows client ref in column A,
then
along each row, shows various monetary values of business done by each
client. The Summary worksheet also shows the client ref in column A, but
I
need this worksheet to sum each of the values entered on a row, but pull
out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Show result of a SUM in a VLOOKUP

From your description this is what your layout looks like:

..........A..........B.........C.........D........ .E
1....Client.....Amt.....Amt.....Amt.....Amt
2.......X..........5..........2.........7......... .4
3.......Y..........1..........4.........6......... .6
4.......Z..........0..........5.........5......... .5

You want the sum for client Y:

=SUMPRODUCT((A2:A4="Y")*B2:E4)

The result is 17.

Biff

"Sarah (OGI)" wrote in message
...
Thank you, but that doesn't seem to work.
What is the 'A2:A10=A2' part of the formula doing? Is it working as a
vlookup?

"T. Valko" wrote:

Try this:

A2 = client

=SUMPRODUCT((Update!A2:A10=A2)*Update!B2:F10)

Biff

"Sarah (OGI)" wrote in message
...
I need to use a vlookup and sum function (I think) in an Excel 2000
workbook
with two worksheets. The Update worksheet shows client ref in column
A,
then
along each row, shows various monetary values of business done by each
client. The Summary worksheet also shows the client ref in column A,
but
I
need this worksheet to sum each of the values entered on a row, but
pull
out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 128
Default Show result of a SUM in a VLOOKUP

Thanks for the help - that is working now - it makes more sense now you've
explained it.

Cheers

"T. Valko" wrote:

From your description this is what your layout looks like:

..........A..........B.........C.........D........ .E
1....Client.....Amt.....Amt.....Amt.....Amt
2.......X..........5..........2.........7......... .4
3.......Y..........1..........4.........6......... .6
4.......Z..........0..........5.........5......... .5

You want the sum for client Y:

=SUMPRODUCT((A2:A4="Y")*B2:E4)

The result is 17.

Biff

"Sarah (OGI)" wrote in message
...
Thank you, but that doesn't seem to work.
What is the 'A2:A10=A2' part of the formula doing? Is it working as a
vlookup?

"T. Valko" wrote:

Try this:

A2 = client

=SUMPRODUCT((Update!A2:A10=A2)*Update!B2:F10)

Biff

"Sarah (OGI)" wrote in message
...
I need to use a vlookup and sum function (I think) in an Excel 2000
workbook
with two worksheets. The Update worksheet shows client ref in column
A,
then
along each row, shows various monetary values of business done by each
client. The Summary worksheet also shows the client ref in column A,
but
I
need this worksheet to sum each of the values entered on a row, but
pull
out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Show result of a SUM in a VLOOKUP

You're welcome. Thanks for the feedback!

Biff

"Sarah (OGI)" wrote in message
...
Thanks for the help - that is working now - it makes more sense now you've
explained it.

Cheers

"T. Valko" wrote:

From your description this is what your layout looks like:

..........A..........B.........C.........D........ .E
1....Client.....Amt.....Amt.....Amt.....Amt
2.......X..........5..........2.........7......... .4
3.......Y..........1..........4.........6......... .6
4.......Z..........0..........5.........5......... .5

You want the sum for client Y:

=SUMPRODUCT((A2:A4="Y")*B2:E4)

The result is 17.

Biff

"Sarah (OGI)" wrote in message
...
Thank you, but that doesn't seem to work.
What is the 'A2:A10=A2' part of the formula doing? Is it working as a
vlookup?

"T. Valko" wrote:

Try this:

A2 = client

=SUMPRODUCT((Update!A2:A10=A2)*Update!B2:F10)

Biff

"Sarah (OGI)" wrote in message
...
I need to use a vlookup and sum function (I think) in an Excel 2000
workbook
with two worksheets. The Update worksheet shows client ref in
column
A,
then
along each row, shows various monetary values of business done by
each
client. The Summary worksheet also shows the client ref in column
A,
but
I
need this worksheet to sum each of the values entered on a row, but
pull
out
the the result of the sum by doing a vlookup on the client ref.
Thanks in advance!








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup shows result one cell above the expected result Marie Excel Worksheet Functions 7 November 14th 06 03:52 AM
show result in msg box Jared Excel Discussion (Misc queries) 4 October 8th 06 09:03 PM
Advanced formula - Return result & Show Cell Reference of result Irv Excel Worksheet Functions 7 May 6th 06 03:36 AM
vlookup based on random result returns incorrect result rickat Excel Worksheet Functions 1 December 6th 05 02:16 PM
Show only Matrix result emel24 Excel Worksheet Functions 3 February 16th 05 05:49 PM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"