| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
| show result in msg box | Excel Discussion (Misc queries) | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
| Show only Matrix result | Excel Worksheet Functions | |||
Hybrid Mode
