formula fix

[JOEE1979]

I'm trying to add up column "K"
I keep getting "#SPILL"
Not sure why,, what did I put that I shouldnt of?

=SUMIFS(K7:K700,I7:I700,(YEAR(I7:I700)=YEAR($L$1)),F7:F700,F7:F700="YES")

Thanks

 

[Eric W]

You're not using the right format for SUMIFS. You can't use functions like you're doing. This is a common misunderstanding. You can use SUMIFS like I did below. This requires using 2 conditions for the date, a lower and an upper bound. Or you can use SUMPRODUCT instead, which gives you a format closer to what you originally had.

Book1
	F	G	H	I	J	K	L
1						9	2024
2						9
3
4
5
6
7	yes			3/1/2024		1
8	yes			4/1/2024		2
9	no			5/1/2024		3
10	yes			6/1/2025		4
11	yes			7/1/2022		5
12	yes			12/31/2024		6
13
Sheet4

Cell Formulas
Range	Formula
K1	K1	=SUMIFS(K7:K700,I7:I700,">="&DATE($L$1,1,1),I7:I700,"<="&DATE($L$1,12,31),F7:F700,"Yes")
K2	K2	=SUMPRODUCT(K7:K700,--(YEAR(I7:I700)=$L$1),--(F7:F700="yes"))

 

[JOEE1979]

You're not using the right format for SUMIFS. You can't use functions like you're doing. This is a common misunderstanding. You can use SUMIFS like I did below. This requires using 2 conditions for the date, a lower and an upper bound. Or you can use SUMPRODUCT instead, which gives you a format closer to what you originally had.

Book1
	F	G	H	I	J	K	L
1						9	2024
2						9
3
4
5
6
7	yes			3/1/2024		1
8	yes			4/1/2024		2
9	no			5/1/2024		3
10	yes			6/1/2025		4
11	yes			7/1/2022		5
12	yes			12/31/2024		6
13
Sheet4

Cell Formulas
Range	Formula
K1	K1	=SUMIFS(K7:K700,I7:I700,">="&DATE($L$1,1,1),I7:I700,"<="&DATE($L$1,12,31),F7:F700,"Yes")
K2	K2	=SUMPRODUCT(K7:K700,--(YEAR(I7:I700)=$L$1),--(F7:F700="yes"))

K2 worked, thanks