Is this lookup sum possible?

[CaraM]

I have a table of accounts, with information about the taxation of withdrawals from those accounts, and account balances by date, like this (and I'm opening to changing the design of this table):

(I just entered simple account balances to see if I could get totals that were correct.)

I want a formula that will sum the account balances, as of the most current listed date, for a specific named taxation category. In other words, in another sheet in my workbook, I will be doing a calculation on all of the money held in accounts that are taxed as ordinary income, etc., as of a specific date.

Can I do a SUMIFS-like calculation while also looking up balances by date? I have a feeling I've done something like this before, but I can't remember what functions I used...

Thanks for any ideas!

 

[jasonb75]

Because you will need to sum multiple rows and multiple columns with criteria your layout is going to need some very clumsy formulas.

As you're open to changing the table, how about dates in column A, account in column B (ira 1, roth 1, etc), account type in column C (ordinary, tax free, etc) and amount in column D?
That way you would be able to simply use sumifs with no lookup or anything else needed.

 

[CaraM]

So, if my table looks like this:

Date	Account	Taxation	Balance
1/31/2021	Traditional IRA 1	Ordinary income	63,729
1/31/2021	Taxable brokerage 1	Cap Gains + Int/Div	15,672
1/31/2021	Traditional IRA 2	Ordinary income	10,000
1/31/2021	Taxable brokerage 2	Cap Gains + Int/Div	7,500
3/30/2021	Traditional IRA 1	Ordinary income	67,553
3/30/2021	Taxable brokerage 1	Cap Gains + Int/Div	17,082
3/30/2021	Traditional IRA 2	Ordinary income	10,700
3/30/2021	Taxable brokerage 2	Cap Gains + Int/Div	7,875

How could I get the total amount of account value taxed as Ordinary Income on or as close to 12/31/2021 as possible? I'm not sure how to use SUMIFS for adding but not on an exact date.

 

[jasonb75]

on or as close to 12/31/2021 as possible?

As close to, or on or before?

Book1
	A	B	C	D
1	Date	Account	Taxation	Balance
2	31/01/2021	Traditional IRA 1	Ordinary income	63729.01
3	31/01/2021	Taxable brokerage 1	Cap Gains + Int/Div	15671.94
4	31/01/2021	Traditional IRA 2	Ordinary income	10000
5	31/01/2021	Taxable brokerage 2	Cap Gains + Int/Div	7500
6	30/03/2021	Traditional IRA 1	Ordinary income	67552.74
7	30/03/2021	Taxable brokerage 1	Cap Gains + Int/Div	17082.48
8	30/03/2021	Traditional IRA 2	Ordinary income	10700
9	30/03/2021	Taxable brokerage 2	Cap Gains + Int/Div	7875
10
11	31/12/2021	Ordinary income	78252.74
Sheet1

Cell Formulas
Range	Formula
D6	D6	=63729*1.06
D7	D7	=15672*1.09
D8	D8	=10000*1.07
D9	D9	=7500*1.05
C11	C11	=SUMIFS(D2:D9,C2:C9,B11,A2:A9,MAXIFS(A2:A9,A2:A9,"<="&A11))

 

[CaraM]

Thanks, @jasonb75 - I replaced the MaxIfs with an xlookup to return the date nearest but before, and it works beautifully!

 

[jasonb75]

return the date nearest but before

That's exactly what maxifs does