Spreadsheet is named “New Revenue Report.xls”
Columns D:O in Worksheet “SLC” represent the 12 months of 2003 (D is January, E February, and so on). Cells D6:O250 contain dollar amounts associated with individuals, who are named in C6:C250 in the same worksheet.
In another Worksheet “Officer Summary” I want to summarize the revenue associated with each individual by month. In this worksheet columns C:N represent the 12 months, and column A contains the names of individual account officers. I would like a formula for cell C4 in “Officer Summary” which would effectively search C6:C250 and D6:D250 of “SLC” to find the total $ amount of revenue for that month associated with an individual officer named in A4 of “Officer Summary”. I don’t care if the formula includes the officer’s name (let’s say “John Smith”) or refers to cell $A$4. I am hoping whatever formula (assuming there is one) will be readily copied over to the right (to cover the remaining months of the year) and down (where I can do a find and replace to substitute the names of other worksheets which represent different product categories).
Just to flesh out the example, in “SLC”:
C6 is “John Smith” D6 is $1,000
C7 is “Jane Doe” D7 is $2,000
C8 is “John Smith” D8 is $11,000
The name “John Smith” would appear in A4 of “Officer Summary”, and the formula in C4 of “Officer Summary” would return $12,000, indicating that this is the amount of revenue which John Smith earned from the product SLC in January 2003.
I know this is pretty complicated, if someone is confident they can solve it I would be glad to e-mail the actual spreadsheet.
Thanks in advance and Happy Holidays to all.
Ludovico
Columns D:O in Worksheet “SLC” represent the 12 months of 2003 (D is January, E February, and so on). Cells D6:O250 contain dollar amounts associated with individuals, who are named in C6:C250 in the same worksheet.
In another Worksheet “Officer Summary” I want to summarize the revenue associated with each individual by month. In this worksheet columns C:N represent the 12 months, and column A contains the names of individual account officers. I would like a formula for cell C4 in “Officer Summary” which would effectively search C6:C250 and D6:D250 of “SLC” to find the total $ amount of revenue for that month associated with an individual officer named in A4 of “Officer Summary”. I don’t care if the formula includes the officer’s name (let’s say “John Smith”) or refers to cell $A$4. I am hoping whatever formula (assuming there is one) will be readily copied over to the right (to cover the remaining months of the year) and down (where I can do a find and replace to substitute the names of other worksheets which represent different product categories).
Just to flesh out the example, in “SLC”:
C6 is “John Smith” D6 is $1,000
C7 is “Jane Doe” D7 is $2,000
C8 is “John Smith” D8 is $11,000
The name “John Smith” would appear in A4 of “Officer Summary”, and the formula in C4 of “Officer Summary” would return $12,000, indicating that this is the amount of revenue which John Smith earned from the product SLC in January 2003.
I know this is pretty complicated, if someone is confident they can solve it I would be glad to e-mail the actual spreadsheet.
Thanks in advance and Happy Holidays to all.
Ludovico
