Need 2 formulas where they lookup values from a different spreadsheet

[Kendoaka]

I have two spreadsheets, book1 & book2. Book1 is automatically updated with all my bank transactions, I manually select a category for each transaction.

Book2 is where I keep track of my Car note payments. I need two formulas, one where it searches for the "Amount" & the other for "TX ID" from book1.

For "Amount" my formula is =SUMIFS(Book1.xlsx!Table1[Amount],Book1.xlsx!Table1[Date],">="&A8,Book1.xlsx!Table1[Date],"<"&A8,Book1.xlsx!Table1[Category],"Car Note")

I am not sure why it's not working.

Thanks,

Book1.xlsx
	A	B	C	D	E
1	Bank Transactions
2	Date	Description	Category	Amount	TX ID
3	6/12/2022	Walmart groceries	Food	$102.13	va25dx
4	6/19/2022	Car payment xxxx091	Car Note	$450.68	am918
5	6/29/2022	Amazon shopping xUS	Shopping	$213.23	0sl81a
6	7/2/2022	Fry's groceries	Food	$109.33	x8ajm2u
7	7/18/2022	Car payment xxx091	Car Note	$447.26	suyth8d
8	7/12/2022	Sam's club TX	Shopping	$176.85	msh71ah
9	8/5/2022	Bashas groceries	Food	$99.39	s83kx7
10	8/19/2022	Car payment xxxx83	Car Note	$445.10	ls01nsh
11	8/21/2022	American Airlines	Travel	$985.63	sk83mpa
12	8/24/2022	Suite hotels	Hotel	$438.74	x7hhyr
13	8/30/2022	Burger king NY	Food	$38.11	js81in
14	9/4/2022	Walmart groceries	Food	$85.49	d6sha
15	9/5/2022	Apple US	New Phone	$1,258.76	28janx
16	9/20/2022	Car payment xxxx83	Car Note	$479.88	s7h5oq
17	9/29/2022	Amazon shopping xUS	Shopping	$78.08	1ksuu8
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C3:C17	List	Food,Car Note,Travel,Hotel,Shopping,Food,New Phone

Book2.xlsx
	A	B	C
1	Car Payments
2	Amount	Amount	TX ID
3	Jan,2022
4	Feb,2022
5	Mar,2022
6	Apr,2022
7	May,2022
8	Jun,2022	$0.00
9	Jul,2022
10	Aug,2022
11	Sep,2022
12	Oct,2022
13	Nov,2022
14	Dec,2022
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SUMIFS(Book1.xlsx!Table1[Amount],Book1.xlsx!Table1[Date],">="&A8,Book1.xlsx!Table1[Date],"<"&A8,Book1.xlsx!Table1[Category],"Car Note")

 

[fjns]

Hi, try this formula for B8
=SUMIFS(Book1.xlsx!Table1[Amount],Book1.xlsx!Table1[Date],">="&A8,Book1.xlsx!Table1[Date],"<"&A9,Book1.xlsx!Table1[Category],"Car Note")

 

[Kendoaka]

Hi, try this formula for B8
=SUMIFS(Book1.xlsx!Table1[Amount],Book1.xlsx!Table1[Date],">="&A8,Book1.xlsx!Table1[Date],"<"&A9,Book1.xlsx!Table1[Category],"Car Note")

Thank you, the formula works but it does not work for C8 ... need help

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Need 2 formulas where they lookup values from a different spreadsheet

I have two spreadsheets, book1 & book2. Book1 is automatically updated with all my bank transactions, I manually select a category for each transaction. Book2 is where I keep track of my Car note payments. I need two formulas, one where it searches for the "Amount" & the other for "TX ID"...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[fjns]

Hi, try this formula for C8
=TEXTJOIN(",",,IF((Book1.xlsx!Table1[Date]>=A8)*(Book1.xlsx!Table1[Date]<A9)*(Book1.xlsx!Table1[Category]="Car Note"),Book1.xlsx!Table1[TX ID],""))

 

[fjns]

Another solution...

formula for B8
=SUM((MONTH(Book1.xlsx!Table1[Date])=MONTH(A8))*(YEAR(Book1.xlsx!Table1[Date])=YEAR(A8))*(Book1.xlsx!Table1[Category]="Car Note")*Book1.xlsx!Table1[Amount])

formula for C8
=TEXTJOIN(",",,IF((MONTH(Book1.xlsx!Table1[Date])=MONTH(A8))*(YEAR(Book1.xlsx!Table1[Date])=YEAR(A8))*(Book1.xlsx!Table1[Category]="Car Note"),Book1.xlsx!Table1[TX ID],""))