# Need formula to search range and associate revenue w/ names

#### Ludovico

##### Board Regular
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Re: Need formula to search range and associate revenue w/ na

Try, in Office Summary cell C4 --

=SUMIF(SLC!\$C\$6:\$C\$250,\$A4,SLC!D\$6:D\$250)

Yes, Perfect! Thanks Jon. eom

nm

Replies
6
Views
980
Replies
17
Views
951
Replies
4
Views
415
Replies
6
Views
202
Replies
11
Views
826

1,203,625
Messages
6,056,387
Members
444,862
Latest member
more_resource23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back