How can I do a SUMIF the YEAR() equals to 2020?

[excelos]

Hello

I have the following data:

Column1: 1, 2, 3,...
Column2: 01/12/1999,01/01/2000,01/02/2000,...

I want to sum the values in Column1 only when the corresponding date in the cell below has a specific year e.g. 2020.

How can I do that?

Thanks!

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Also please post some sample data using the XL2BB add-in.

 

[excelos]

I updated my details but I cannot install the plug in as it is not allowed for my version.

The data is:

01/01/2000​	1​
01/06/2000​	2​
01/11/2000​	3​
01/04/2001​	4​
01/09/2001​	5​
01/02/2002​	6​
01/07/2002​	7​
01/12/2002​	8​
01/05/2003​	9​
01/10/2003​	10​
01/03/2004​	11​
01/08/2004​	12​
01/01/2005​	13​

I want to create a third column which will display the sum of the second column for the cells that the first column has the same year.

E.g. the first cell in the third column (C1) should return the sum of the B1,B2,B3 cells of the second column because these cells have the same year as the cell A1

 

[Fluff]

Ok how about

+Fluff v2.xlsm
	A	B	C
1
2	01/01/2000	1	6
3	06/01/2000	2	6
4	11/01/2000	3	6
5	04/01/2001	4	9
6	09/01/2001	5	9
7	02/01/2002	6	21
8	07/01/2002	7	21
9	12/01/2002	8	21
10	05/01/2003	9	19
11	10/01/2003	10	19
12	03/01/2004	11	23
13	08/01/2004	12	23
14	01/01/2005	13	13
15
16
Work

Cell Formulas
Range	Formula
C2:C14	C2	=SUM(FILTER($B$2:$B$20,YEAR($A$2:$A$20)=YEAR(A2)))

 

[excelos]

Brilliant thanks!

I tried SUMIF($B$2:$B$20,YEAR($A$2:$A$20)=YEAR(A2)) or SUMIFS($B$2:$B$20,YEAR($A$2:$A$20),YEAR(A2)) but it didn't work, any idea?

 

[Dave Patton]

T202011a.xlsm
	A	B	C	D	E	F
1	Jan 1, 2000	1	6	Year
2	Jan 2, 2000	2	6	2003	21	21
3	Jan 3, 2000	3	6
4	Jan 4, 2001	4	9
5	Jan 5, 2001	5	9
6	Jan 6, 2002	6	30
7	Jan 7, 2002	7	30
8	Jan 8, 2002	8	30
9	Jan 9, 2002	9	30
10	Jan 10, 2003	10	21
11	Jan 11, 2003	11	21
12	Jan 12, 2004	12	25
13	Jan 13, 2004	13	25
14	Jan 14, 2005	14	14
3c

Cell Formulas
Range	Formula
E2	E2	=SUMIFS(B:B,A:A,">="&DATE(D2,1,1),A:A,"<="&DATE(D2,12,31))
F2	F2	=SUMPRODUCT(--(YEAR($A$1:$A$14)=D2),$B$1:$B$14)
C1:C14	C1	=SUMPRODUCT(--(YEAR($A$1:$A$14)=YEAR(A1)),$B$1:$B$14)

 

[Fluff]

Brilliant thanks!

You're welcome & thanks for the feedback.