#### lmcd5666

##### New Member
Situation:

A1 B1 C1 D1 E1
1 10/15/02 Joe John 100 100
2 10/15/02 John Joe 200 200
3 10/15/02 Joe Joe 300 -0-

Amount in D1 corresponds to name in B1 and amount in E1 corresponds to name in C1 and so forth down the rows.

Is there a way to make a report that would pull the name only once from column B1 & C1 and display (and total) the corresponding amounts in D1 and E1. For example, from above, the report would display:

Joe 100
200
300
_0__
Total 600

The report doesn't have to appear exactly as shown. I just want some ways of extracting the numbers (and totaling it) corresponding to the name. Is it possible?
Any help would be greatly appreciate.
Thank you.

Toan

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
if you would be willing to rearrange your data so that each instance in which a name had an amount attached to it had it's own row (instead of John and Joe both having an amount in row 1 for example), autofilter would probably give you the results you needed. you could use autofilter to display just the data you wanted to see, and you could then use the formula:

=SUBTOTAL(9,D:D)

to give you a total amount for just the filter data, not all of the data in the column - assuming in this formula that column D will house your amounts.

HTH
kevin
This message was edited by kskinne on 2002-10-02 17:52

Kevin,

Thanks for the reply, however, I can not change the two columns (b1 & c1) with names nor can I change the two columns (d1 & e1)with amounts. There are formulae in D1 that compare the names of b1 & c1 and split the amount of column z1 if the names of b1 & c1 are not the same. Otherwise, d1 will get the entire amount. It does this for every rows. Any thoughts???

Toan

On 2002-10-02 18:13, lmcd5666 wrote:
Kevin,

Thanks for the reply, however, I can not change the two columns (b1 & c1) with names nor can I change the two columns (d1 & e1)with amounts. There are formulae in D1 that compare the names of b1 & c1 and split the amount of column z1 if the names of b1 & c1 are not the same. Otherwise, d1 will get the entire amount. It does this for every rows. Any thoughts???

Toan

try the following

=SUMPRODUCT((B1:B3="Joe")*(D1:D3)+(C1:C3="Joe")*(B1:B3<>"Joe")*(E1:E3))

with the name in a cell say G1 the formula is

=SUMPRODUCT((B1:B3=G1)*(D1:D3)+(C1:C3=G1)*(B1:B3<>G1)*(E1:E3))
This message was edited by Dave Patton on 2002-10-02 18:41

Replies
6
Views
609
Replies
3
Views
394
Replies
7
Views
371
Replies
14
Views
939
Replies
3
Views
581

1,218,620
Messages
6,143,515
Members
450,492
Latest member
Rusbus1972

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