Conditional Formula Help

[chet645]

All, I could really use your help finding a time saving formula. I am trying to extract data from the spreadsheet below that has several thousand rows of data (see table 1 below) to create a nice flat table that I can easily sort, chart, pivot etc. (see table 2 below). In short, I am trying to create a conditional formula in table 2 (cells H4:L4) that will pull the "Amount" in column H of table 1, conditional on the "Charge Code" in column G of table 1. Any help would be much appreciated and would save me a significant amount of time. Thanks in advance.

Table 1:

AZUL Rent Roll 1.27.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Unit	Unit Type	Unit	Resident	Name	Market	Charge	Amount	Resident	Other	Move In	Lease	Move Out	Balance
6			Sq Ft			Rent	Code		Deposit	Deposit		Expiration
7	Current/Notice/Vacant Residents
8	A01	1/1B DEV	590.00	t0004335	Jesus Sanchez	1,655.00	1pet	65.00	400.00	500.00	10/16/2021	8/31/2023		128.50
9							1rent	1,539.00
10							Total	1,604.00
11
12	A02	1/1B DEV	590.00	t0004045	Melissia Sanchez	1,655.00	1rent	1,167.00	900.00	0.00	10/7/2020	10/31/2022		0.00
13							Total	1,167.00
14
15	A03	1/1B DEV	590.00	VACANT	VACANT	1,655.00		0.00	0.00	0.00				0.00
16							Total	0.00
17
18	A04	1/1B DEV	590.00	t0004047	Breana Henry	1,655.00	1rent	1,215.00	1,119.00	0.00	3/11/2021	3/10/2022		556.73
19							Total	1,215.00
20
21	A05	1/1BX	590.00	t0005923	. Divinity Prophet and Associates	1,805.00	1rent	1,965.00	500.00	0.00	8/8/2022	7/5/2023		-4.38
22							Total	1,965.00
23
24	A06	1/1B DEV	590.00	t0004049	Tina Liukko	1,655.00	1rent	1,210.00	400.00	0.00	9/25/2018	6/7/2022		0.00
25							Total	1,210.00
26
27	A07	2/2B DEV	880.00	t0004547	Christian Villagomez	1,985.00	1rent	1,800.00	500.00	0.00	11/15/2021	10/14/2022		186.57
28							Total	1,800.00
29
30	A08	2/2B DEV	880.00	VACANT	VACANT	1,985.00		0.00	0.00	0.00				0.00
31							Total	0.00
32
33	A09	2/2B DEV	880.00	t0004841	Brenda Zurita	1,985.00	1rent	2,145.00	900.00	0.00	6/9/2022	5/8/2023		272.49
34							Total	2,145.00
35
36	A10	2/2B DEV	880.00	t0004053	Michael Malet	1,985.00	1rent	1,623.00	3,454.00	0.00	8/8/2021	7/31/2023		-1.53
37							Total	1,623.00
38
39	A11	1/1B DEV	590.00	t0004054	Sara Canelli *EV	1,655.00	1rent	1,078.00	539.00	0.00	6/18/2020	6/17/2021	2/18/2023	7,734.63
40							Total	1,078.00
41
42	A12	1/1B DEV	590.00	t0004055	Sandra Hutchinson	1,655.00	1rent	1,255.00	400.00	0.00	5/7/2021	5/6/2022		3.65
43							Total	1,255.00
44
45	A13	1/1B DEV	590.00	t0004056	Heather Sharp	1,655.00	1rentprk	75.00	400.00	0.00	11/18/2020	11/17/2021		-128.50
46							1rent	1,203.00
47							Total	1,278.00
48
49	A14	1/1B DEV	590.00	t0004840	Brenda Esquivel	1,655.00	1rent	1,735.00	400.00	0.00	6/1/2022	4/30/2023		0.00
50							Total	1,735.00
51
52	A15	1/1B DEV	590.00	t0004058	Nathanael Nash	1,655.00	1rentprk	75.00	400.00	0.00	4/1/2021	3/31/2022		0.00
53							1rent	1,255.00
54							Total	1,330.00
55
56	A16	1/1BX	590.00	t0006137	Herbert Scott	1,805.00	1rent	1,605.00	400.00	0.00	11/25/2022	10/24/2023		-0.91
57							Total	1,605.00
Report1

Table 2:

AZUL Rent Roll 1.27.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2								Rent Type
3		Unit	Unit Type	SF	Resident Code	Move-In	Lease Exp.	1pet	1rent	1rentprk	1rensec8	1renman
4		A01	1/1B DEV	590	t0004335	10/16/21	8/31/23	65	0	0	0	0
5		A02	1/1B DEV	590	t0004045	10/7/20	10/31/22
6		A03	1/1B DEV	590	VACANT	1/0/00	1/0/00
7		A04	1/1B DEV	590	t0004047	3/11/21	3/10/22
8		A05	1/1BX	590	t0005923	8/8/22	7/5/23
9		A06	1/1B DEV	590	t0004049	9/25/18	6/7/22
10		A07	2/2B DEV	880	t0004547	11/15/21	10/14/22
11		A08	2/2B DEV	880	VACANT	1/0/00	1/0/00
12		A09	2/2B DEV	880	t0004841	6/9/22	5/8/23
13		A10	2/2B DEV	880	t0004053	8/8/21	7/31/23
14		A11	1/1B DEV	590	t0004054	6/18/20	6/17/21
15		A12	1/1B DEV	590	t0004055	5/7/21	5/6/22
16		A13	1/1B DEV	590	t0004056	11/18/20	11/17/21
17		A14	1/1B DEV	590	t0004840	6/1/22	4/30/23
18		A15	1/1B DEV	590	t0004058	4/1/21	3/31/22
19		A16	1/1BX	590	t0006137	11/25/22	10/24/23
20		B01	1/1B DEV	590	t0004320	9/8/21	8/7/22
21		B02	1/1BX	590	t0006193	11/4/22	10/3/23
Sheet1

Cell Formulas
Range	Formula
F4:F21	F4	=SUMIFS(Report1!$K$8:$K$947,Report1!$D$8:$D$947,Sheet1!$E4)
G4:G21	G4	=SUMIFS(Report1!$L$8:$L$947,Report1!$D$8:$D$947,Sheet1!$E4)
H4:L4	H4	=SUMIFS(Report1!$H$8:$H$947,Report1!$D$8:$D$947,Sheet1!$E4,Report1!$G$8:$G$947,Sheet1!H$3)

 

[chet645]

Don't mean to pester the group, but trying one last time to see if someone has any suggestions. Let me know if something is not clear.