Filtering dates

[tigerzen]

I have stumbled across this formula which calculates YTD spanning multiple years. I don't really understand what the *($A$3:$A$26<=$A3) part does in the formula. Without it, the formula does not work. I'd like to understand it a little better and perhaps the formula I have is not the best or most efficient way of calculating YTD when you have multiple years so am open to suggestions on how one might calculate YTDs within their particular years if you have data spanning many years.

Book10
	A	B	C
1
2	Month	Amount	YTD
3	1/01/2019	1,256	1,256
4	28/02/2019	1,369	2,625
5	31/03/2019	1,018	3,643
6	30/04/2019	1,406	5,049
7	31/05/2019	992	6,041
8	30/06/2019	1,364	7,405
9	31/07/2019	1,497	8,902
10	31/08/2019	1,322	10,224
11	30/09/2019	1,042	11,266
12	31/10/2019	1,214	12,480
13	30/11/2019	939	13,419
14	31/12/2019	1,284	14,703
15	31/01/2020	1,073	1,073
16	29/02/2020	1,424	2,497
17	31/03/2020	1,283	3,780
18	30/04/2020	973	4,753
19	31/05/2020	1,063	5,816
20	30/06/2020	971	6,787
21	31/07/2020	1,440	8,227
22	31/08/2020	1,191	9,418
23	30/09/2020	1,103	10,521
24	31/10/2020	882	11,403
25	30/11/2020	1,480	12,883
26	31/12/2020	1,383	14,266
Sheet1

Cell Formulas
Range	Formula
C3:C26	C3	=SUM(FILTER($B$3:$B$26,(YEAR($A$3:$A$26)=YEAR($A3))*($A$3:$A$26<=$A3)))

 

[FormR]

Hi, that part is specifically restricting the filtered rows to those that are less than or equal to the date in column A for the row the formula is on.

SUMIFS() might be a little more efficient, but I doubt there's much in it either way.

Book1
	A	B	C
1
2	Month	Amount	YTD
3	01/01/2019	1256	1256
4	28/02/2019	1369	2625
5	31/03/2019	1018	3643
6	30/04/2019	1406	5049
7	31/05/2019	992	6041
8	30/06/2019	1364	7405
9	31/07/2019	1497	8902
10	31/08/2019	1322	10224
11	30/09/2019	1042	11266
12	31/10/2019	1214	12480
13	30/11/2019	939	13419
14	31/12/2019	1284	14703
15	31/01/2020	1073	1073
16	29/02/2020	1424	2497
17	31/03/2020	1283	3780
18	30/04/2020	973	4753
19	31/05/2020	1063	5816
20	30/06/2020	971	6787
21	31/07/2020	1440	8227
22	31/08/2020	1191	9418
23	30/09/2020	1103	10521
24	31/10/2020	882	11403
25	30/11/2020	1480	12883
26	31/12/2020	1383	14266
Sheet1

Cell Formulas
Range	Formula
C3:C26	C3	=SUMIFS($B$3:$B$26,$A$3:$A$26,">="&DATE(YEAR(A3),1,1),$A$3:$A$26,"<="&A3)

 

[awoohaw]

It is matrix math
(YEAR($A$3:$A$26)=YEAR($A3))*($A$3:$A$26<=$A3)), has two factors:
1. YEAR($A$3:$A$26)=YEAR($A3)
2. ($A$3:$A$26<=$A3)

when those conditions are true the matrix math give a number other than zero.
the filter function uses only the cells with non zero values from column A, and selects the cells in column B relative to those cells.

 

[tigerzen]

Hi, that part is specifically restricting the filtered rows to those that are less than or equal to the date in column A for the row the formula is on.

SUMIFS() might be a little more efficient, but I doubt there's much in it either way.

Book1
	A	B	C
1
2	Month	Amount	YTD
3	01/01/2019	1256	1256
4	28/02/2019	1369	2625
5	31/03/2019	1018	3643
6	30/04/2019	1406	5049
7	31/05/2019	992	6041
8	30/06/2019	1364	7405
9	31/07/2019	1497	8902
10	31/08/2019	1322	10224
11	30/09/2019	1042	11266
12	31/10/2019	1214	12480
13	30/11/2019	939	13419
14	31/12/2019	1284	14703
15	31/01/2020	1073	1073
16	29/02/2020	1424	2497
17	31/03/2020	1283	3780
18	30/04/2020	973	4753
19	31/05/2020	1063	5816
20	30/06/2020	971	6787
21	31/07/2020	1440	8227
22	31/08/2020	1191	9418
23	30/09/2020	1103	10521
24	31/10/2020	882	11403
25	30/11/2020	1480	12883
26	31/12/2020	1383	14266
Sheet1

Cell Formulas
Range	Formula
C3:C26	C3	=SUMIFS($B$3:$B$26,$A$3:$A$26,">="&DATE(YEAR(A3),1,1),$A$3:$A$26,"<="&A3)

Okay thanks for explaining it further, the formula must be acting as some sort of counter, I think I like your formula more than mine.

 

[awoohaw]

@tigerzen it doesn't act as a counter, the sumifs is also just like the filter criteria, behind the scenes it is using matrix math, and calculating TRUES and FALSES. and passing the value to include from the relative cells in the sum when all of the conditions are TRUE.

 

[tigerzen]

@tigerzen it doesn't act as a counter, the sumifs is also just like the filter criteria, behind the scenes it is using matrix math, and calculating TRUES and FALSES. and passing the value to include from the relative cells in the sum when all of the conditions are TRUE.

Thanks awoohaw for clarifying.