Lookup - match - Add & Subtract

[Vcoppens]

Hi,
I have a spreadsheet with 3 tabs:
1. TOTAL: Where the results need to show (= total sales per owner) and where formulas need to be entered
2. RENT: Sales and refund per property for one particular month
3. OWNER: Property owners and their corresponding property numbers

The first step should be:
Lookup the name in the column A of tab1, if it matches the one in column A in tab 3 then take the property # which is in column B

The second step should be:
Lookup those particular property #'s in tab 2 and add the rental income minus the refunds

Your help will be appreciated helping create a formula.
Total

	A	B	C	D	E	F
1	Name	Jan-17	Feb-17	Mar-17	Apr-17
2	Jones					Total
3	Smith					Total
4		Total	Total	Total	Total

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
Rent

	A	B	C	D	E	F	G
1	Property		Rent income
2		Refund	Jan-17	Refund	Feb-17	Refund	Mar-17
3	1		200		200		300
4	2	50	300		150		70
5	3		100		200		150
6	4		50	10	100		200
7	5		150		300		100
8	6		200		70		300
9	7	20	100		150	30	100
10	8		300		200		50
11	9		70		70		150
12	1		150	20	150		200
13	2		200		200		100
14	3		100		100		300
15	4		300		300		70
16	5	15	70		100		150
17	6		200		50		200
18	7		300		150		300
19	8		100		100		100
20	9		50		50		50
21		85	2940	30	2640	30	2890

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 45.6px;"><col style="width: 56px;"><col style="width: 48.8px;"><col style="width: 60px;"><col style="width: 46.4px;"><col style="width: 54.4px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B21 =SUM(B3:B20) C21 =SUM(C3:C20) D21 =SUM(D3:D20) E21 =SUM(E3:E20) F21 =SUM(F3:F20) G21 =SUM(G3:G20) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Owner

	A	B
1	Name	Property #
2	smith	1
3	smith	2
4	smith	3
5	jones	4
6	jones	5
7	smith	6
8	jones	7
9	jones	8
10	smith	9

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Vcoppens]

Thank you for trying to fix this.

The spreadsheet has 3 tabs.
The first tab is where the formula needs to be entered and reflects the total per owner
The second tab has the sales per property. Since multiple sales can happen per property, a property might show up several times per month,
The third tab has the name of the owner and the properties that belong to that owner. The properties are defines in the form of a number.

When I copied the formula, I get the following message.
Hope this helps....
"We found a problem with the formula. Try clicking insert Function". Not trying to type a formula, when the first character is an equal etc." The usual notice of excel"

 

[Vcoppens]

I think I have a partial solution.
In the Rent tab I could add a column to the left of the properties and add the following formula
=VLOOKUP(a3,owner!$a$2:$b$10,2,0)

That way I have on the rent tab the name of the owner and the corresponding property.

Then I can add in the total tab the total income for each owner and their corresponding properties.

Will be working on that one now.

 

[Vcoppens]

I think I have it.
In the first tab, the following formula should give the result
=SUMPRODUCT(--(F3:F20=A2),I3:I20-H3:H20)

Total

	A	B	C	D
1	Name	January	February	March
2	Jones	1335	1390	1040
3	Smith	1520	1220	1820
4		Total	Total	Total

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(--($F$3:$F$20=$A2),I$3:I$20-H$3:H$20) C2 =SUMPRODUCT(--($F$3:$F$20=$A2),K$3:K$20-J$3:J$20) D2 =SUMPRODUCT(--($F$3:$F$20=$A2),M$3:M$20-L$3:L$20) B3 =SUMPRODUCT(--($F$3:$F$20=$A3),I$3:I$20-H$3:H$20) C3 =SUMPRODUCT(--($F$3:$F$20=$A3),K$3:K$20-J$3:J$20) D3 =SUMPRODUCT(--($F$3:$F$20=$A3),M$3:M$20-L$3:L$20) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

Great you found a solution.

M.

 

[Vcoppens]