Adding from two columns

lmcd5666

New Member
Joined
Sep 5, 2002
Messages
20
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top