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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

lmcd5666

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

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
Top