vba to sumif the value by year

[abhi_jain80]

Hi experts,

I need to sumif the values of column "AO" if the year in column "AN" is 2021 and need the result in different worksheet range say A2. can somebody help me please?
Mini sheet attached for your reference. As per this sample data, the result should be 296

Apologies if this has been solved earlier and thanks in advance...

Spares Analytics ToolKit_v3.xlsb
	AN	AO
1	Date	$ Value
2	21/06/2021	34
3	21/07/2021	32
4	01/06/2021	3
5	17/06/2021	2
6	12/06/2021	2
7	26/08/2022	1
8	01/06/2021	1
9	01/06/2020	1
10	01/06/2020	3
11	01/06/2020	3
12	01/06/2021	2
13	02/06/2021	3
14	08/02/2020	442
15	08/03/2022	22
16	05/06/2021	12
17	11/09/2021	21
18	03/06/2021	0
19	18/05/2021	184
20	27/01/2022	-87
Analytics Summary

 

[Joe4]

Not sure why you need VBA when a simple formula will do:

=SUMPRODUCT(--(YEAR(AN2:AN20)=2021),--(AO2:AO20))

 

[abhi_jain80]

Not sure why you need VBA when a simple formula will do:

=SUMPRODUCT(--(YEAR(AN2:AN20)=2021),--(AO2:AO20))

This is a part of big vba script, hence i need the vba code only. I am trying to use the formula you have suggested but not been able to. can you please have a look?

    Dim lastRow As Long
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "AM").End(xlUp).Row
    End With
    
    With Worksheets("Sheet2").Range("A1")
        .Formula = "=SUMPRODUCT(--(YEAR('Sheet1'!AN2:AN" & lastRow & "=2021")),--('Sheet1'!AO2:AO" & lastRow & "))"
        .Value = .Value
    End With
   End Sub

 

[Joe4]

Temporarily remove the:

        .Value = .Value

line.

Then it will leave the formula in there, and you can verify if it looks right.

 

[abhi_jain80]

Temporarily remove the:

        .Value = .Value

line.

Then it will leave the formula in there, and you can verify if it looks right.

The formula doesn't looks right. I am not able to correct it. Can you please suggest the right formula? Thanks

 

[Joe4]

Please post exactly what the formula looks like.