Sumifs and subtotal (or not)?

[deLockloire]

I have a table with 3 columns: transaction date, costs/revenues, tax/deductable tax. Each row is one revenue or cost and the dates goes on multi-year seamlessly (i.e., I have a table that has this from 2019). What I'd like is to give a date in a cell (say A1), and have a formula that outputs a special sumif in b1 with the following rule:

1) it should sum all costs/revenues up to the date given in A1. (easy, simple sumif function, however...)
2) it should sum all tax/dedutcable tax as well up to the date indicated in A1, but the trick here is that this column should be evaluated separately for each year, and it cannot go below 0 in any given year. So if, say, in 2019 the sum of all revenues/costs is 10,000, the sum of all tax/deductables is 1000, and in 2020 the revenues are 100 and the taxes/deductables are -300, and the date is indicated in A1 is 2020.12.31, it should output 11,100 (10,000+1,000 for 2019 and 100+0 for 2020).

Is there a formula to solve this? Some form of subtotal or something?

Thanks for the tips!

 

[awoohaw]

it would really help if you could post a mini workbook that the forum can copy and paste and work with the data. You can anonymize it if needed.

 

[awoohaw]

yeah, i tried to build what you're asking, but I have no clue. Care to give better examples? Maybe use the xl2bb add in to give us what you actually have to work with?

Book1
	B	C	D	E	F							M	N	O
13
14		rev/costs	Tax/Ded									transaction date	costs/revenues	tax/deductable tax
15	2019	10000	1000									2020-03-10	-100	-100
16	2020	100	-300		11100							2019-02-03	200	-200
17												2021-08-19	0	0
18												2019-10-18	-300	-200
19												2023-09-10	300	-100
20												2021-08-27	-300	-200
21												2019-03-11	-300	-100
22												2019-10-13	200	-200
23												2022-08-06	-100	0
24												2023-01-06	-300	0
25												2020-01-03	100	0
26												2019-02-22	200	-200
27												2022-03-27	-200	0
28												2021-07-14	-300	-100
29												2023-02-23	100	-200
30												2019-08-03	300	-200
31												2019-09-20	100	-100
32												2023-11-22	-100	-200
33												2023-09-04	200	-200
34												2022-06-08	-100	0
35
Sheet2