Extracting offset data from two lists where the originals do not match 100%

[craigmack]

I have a task every quarter to produce a real estate agent production list. Each sale has two halves, a listing half, and a selling half. ie a $10,000 commission has a $5,000 list side & a $5,000 sell side. One agent can 'get' both sides or it can be two different agents.


At the moment I use a pivot table to generate a list totalling the dollars in list side transactions, then the sell sides. Each time copying and pasteing two columns, 'agent name' & 'amount'. So I end up with 4 columns. Although knowing each side has some value, the main value is to know the total commission dollars by agent. Unfortunately not every agent gets a list side, not every agent gets a sell side.


My problem is coming up with a formula to cover this.

ie from this:


Name List-amount Name Sell_amount

A 1000 A 1500

B 500 C 400

D 750 D 1500

E 350 F 750

F 900


to this:


Name Amount

A 2500

B 500

C 400

D 2250

E 350

F 1650


Thank you for any help

 

[SirJB7]

Hi, craigmack!

Asumming your data is from A1:D6 and that you can get a list of all agents (not need to be sorted) from F2 down, you can write in G2 and copy down:

=SUMAR.SI(A:A;F2;B:B)+SUMAR.SI(C:C;F2;D:D) -----> in english: =SUMIF(A:A,F2,B:B)+SUMIF(C:C,F2,D:D)

Regards!

 

[srinidhi]

There are many workarounds, but I guess this is the most simple & easiest solution. In a new tab, first copy & paste the data Name & List-amount below that copy & paste the data Name & sell-amount(without the heading), now in a single pivot you will get the sum of the amount against all the names.

 

[craigmack]

Kudos SirJB7! You have saved me hours of cutting & pasting blank cells to make things match up. Not sure I understand how the formula works, but it does, works like a charm. Thank you!

 

[SirJB7]

Hi, craigmack!

Each term of the formula checks within a range (column A in first term and column C in second term) if each cell is equal to a given value (cell Fn in both terms, where n is the row number), and if they're equal then it adds the corresponding value of an associated range (column B in first term and column D in second term).

If you wanna check Excel help for any function, select any empty cell, then go to the Formula tab, then click on Insert function icon, and a pop-up windows will appear that lets you select functions by name, category, and selecting one you can press "Help on this function" (or something like that as a link at bottom left, my Excel is in spanish). There you can read the full explanation and details for each formula.

Regards!