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

 

[Marcelo Branco]

Maybe this...

Formula in B2 of Sheet Total
=IFERROR(SUMPRODUCT(--ISNUMBER(MATCH(Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX(Rent!$B$3:$G$20,0,MATCH(B$1,Rent!$B$2:$G$2,0))+INDEX(Rent!$B$3:$G$20,0,MATCH(B$1,Rent!$B$2:$G$2,0)-1)),0)

Copy across till E2 and down

M.

 

[Marcelo Branco]

Sorry, i misread your post. You need Rental - Refund

Correction
Formula in B2 of Sheet Total
=IFERROR(SUMPRODUCT(--ISNUMBER(MATCH(Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX(Rent!$B$3:$G$20,0,MATCH(B$1,Rent!$B$2:$ G$2,0))-INDEX(Rent!$B$3:$G$20,0,MATCH(B$1,Rent!$B$2:$G$2,0)-1)),0)

Copy across till E2 and down

M.

 

[Vcoppens]

Thank you so much for your quick reply.

I tried to enter the formula but it returns a 0 result

Could it be that the in the section INDEX(Rent!$B$3:$G$20,0,MATCH(B$1,Rent!$B$2:$ G$2,0) the value should be a c value (= the January income) $C$3:$c$20 rather than a b value (= the refund)?

Thank you very much,

 

[Marcelo Branco]

It worked for me

	A​	B​	C​	D​	E​
1​	Name​	jan/17​	fev/17​	mar/17​	abr/17​
2​	Jones​	1335​	1390​	1040​	0​
3​	Smith​	1520​	1220​	1820​	0​

<tbody>
</tbody>

Remark: fev/17 (Portuguese) = feb/17 (English)

It should work for you, too.

Check
1. If the dates in Total!:B1:C1 and Rent!B2:G2 are real dates, not texts
Try in an empty cell
=ISNUMBER(Total!B1)

2. If the headers (dates) in sheet Total B1:E1 are identical to the dates in Sheet Rent B2:G2.
For example, try in an empty cell
=Total!B1=Rent!C2
Tell us the result (hope it returns TRUE)

M.

 

[Vcoppens]

It worked for me

	A​	B​	C​	D​	E​
1​	Name​	jan/17​	fev/17​	mar/17​	abr/17​
2​	Jones​	1335​	1390​	1040​	0​
3​	Smith​	1520​	1220​	1820​	0​

<tbody>
</tbody>

Remark: fev/17 (Portuguese) = feb/17 (English)

It should work for you, too.

Check
1. If the dates in Total!:B1:C1 and Rent!B2:G2 are real dates, not texts
Try in an empty cell
=ISNUMBER(Total!B1)

2. If the headers (dates) in sheet Total B1:E1 are identical to the dates in Sheet Rent B2:G2.
For example, try in an empty cell
=Total!B1=Rent!C2
Tell us the result (hope it returns TRUE)

M.

The result returns true - let me try your formula again. Need to head out right now, will look at it a little later. Thank you very much for helping.

 

[Marcelo Branco]

Ok. I hope you can identify and fix the problem.

M.

 

[Vcoppens]

This is my result ...
Total

	A	B	C	D	E	F
1	Name	Jan-17	Feb-17	Mar-17	Apr-17
2	Jones	0	0	0	0	Total
3	Smith	0	0	0	0	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>

Spreadsheet Formulas

Cell Formula B2 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(B$1,[Rent]Rent!$B$2:$G$2,0))-INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(B$1,[Rent]Rent!$B$2:$G$2,0)-1)),0) C2 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(C$1,[Rent]Rent!$B$2:$G$2,0))-INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(C$1,[Rent]Rent!$B$2:$G$2,0)-1)),0) D2 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(D$1,[Rent]Rent!$B$2:$G$2,0))-INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(D$1,[Rent]Rent!$B$2:$G$2,0)-1)),0) E2 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Rent!$A$3:$A$20,(Owner!$A$2:$A$10=$A2)*Owner!$B$2:$B$10,0)),INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(E$1,[Rent]Rent!$B$2:$G$2,0))-INDEX([Rent]Rent!$B$3:$G$20,0,MATCH(E$1,[Rent]Rent!$B$2:$G$2,0)-1)),0) B3 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Total!$A$3:$A$20,(Owner!$A$2:$A$10=$A3)*Owner!$B$2:$B$10,0)),INDEX([Rent]Total!$B$3:$G$20,0,MATCH(B$1,[Rent]Total!$B$2:$G$2,0))-INDEX([Rent]Total!$B$3:$G$20,0,MATCH(B$1,[Rent]Total!$B$2:$G$2,0)-1)),0) C3 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Total!$A$3:$A$20,(Owner!$A$2:$A$10=$A3)*Owner!$B$2:$B$10,0)),INDEX([Rent]Total!$B$3:$G$20,0,MATCH(C$1,[Rent]Total!$B$2:$G$2,0))-INDEX([Rent]Total!$B$3:$G$20,0,MATCH(C$1,[Rent]Total!$B$2:$G$2,0)-1)),0) D3 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Total!$A$3:$A$20,(Owner!$A$2:$A$10=$A3)*Owner!$B$2:$B$10,0)),INDEX([Rent]Total!$B$3:$G$20,0,MATCH(D$1,[Rent]Total!$B$2:$G$2,0))-INDEX([Rent]Total!$B$3:$G$20,0,MATCH(D$1,[Rent]Total!$B$2:$G$2,0)-1)),0) E3 =IFERROR(SUMPRODUCT(--ISNUMBER(MATCH([Rent]Total!$A$3:$A$20,(Owner!$A$2:$A$10=$A3)*Owner!$B$2:$B$10,0)),INDEX([Rent]Total!$B$3:$G$20,0,MATCH(E$1,[Rent]Total!$B$2:$G$2,0))-INDEX([Rent]Total!$B$3:$G$20,0,MATCH(E$1,[Rent]Total!$B$2:$G$2,0)-1)),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

I can see now the problem. You are using Excel Tables. I hadn't noticed that.
This makes the formulas more complicated especially because we need to copy/drag them across and down
About this issue, see
https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

That said, see if this works for you
Total table: gray area
Rent table: light green area
Owner table: light blue area.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

Name​

jan/17​

fev/17​

mar/17​

abr/17​

Property​

Refund​

jan/17​

Refund2​

fev/17​

Refund3​

mar/17​

Name​

Property #​

2​

Jones​

1335​

1390​

1040​

0​

1​

200​

200​

300​

smith​

1​

3​

Smith​

1520​

1220​

1820​

0​

2​

50​

300​

150​

70​

smith​

2​

4​

3​

100​

200​

150​

smith​

3​

5​

4​

50​

10​

100​

200​

jones​

4​

6​

5​

150​

300​

100​

jones​

5​

7​

6​

200​

70​

300​

smith​

6​

8​

7​

20​

100​

150​

30​

100​

jones​

7​

9​

8​

300​

200​

50​

jones​

8​

10​

9​

70​

70​

150​

smith​

9​

11​

1​

150​

20​

150​

200​

12​

2​

200​

200​

100​

13​

3​

100​

100​

300​

14​

4​

300​

300​

70​

15​

5​

15​

70​

100​

150​

16​

6​

200​

50​

200​

17​

7​

300​

150​

300​

18​

8​

100​

100​

100​

19​

9​

50​

50​

50​

20​

<tbody>
</tbody>

Formula in B2 copied across and down
=IFERROR(SUMPRODUCT(--ISNUMBER(MATCH(Rent[[Property]:[Property]],(Owner[[Name]:[Name]]=Total[@[Name]:[Name]])*Owner[[Property '#]:[Property '#]],0)),INDEX(Rent[[Refund]:[mar/17]],0,MATCH(B$1,Rent[[#Headers],[Refund]:[mar/17]],0))-INDEX(Rent[[Refund]:[mar/17]],0,MATCH(B$1,Rent[[#Headers],[Refund]:[mar/17]],0)-1)),0)

M.

 

[Marcelo Branco]

I'm confused...
It seems that you have only two tables Rent and Owner.
Total is not a table

If this is correct, try in B2 of sheet Total
=IFERROR(SUMPRODUCT(--ISNUMBER(MATCH(Rent[[Property]:[Property]],(Owner[[Name]:[Name]]=$A2)*Owner[[Property '#]:[Property '#]],0)),INDEX(Rent,0,MATCH(B$1,Rent[#Headers],0))-INDEX(Rent,0,MATCH(B$1,Rent[#Headers],0)-1)),0)

copy across and down

M.