vba code to sum the values of a column by year

**abhi_jain80** · 06-18-2021, 04:21 PM

Hi experts,

Please refer the sample data attached. I need to sum the values of column B if the year in column A is 2021 in "Sheet1" and need the result in "Sheet2". There are no fixed number of rows in the columns.
I need the vba script but I am struggling to write it. Can somebody help me please?

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

Abhi

**TMS** · 06-18-2021, 06:08 PM

One way:

Option Explicit

Sub sTotal2021()

Dim lLR As Long
lLR = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

' fixed range
Sheet2.Range("A1").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR($A$2:$A$984)=2021), --($B$2:$B$984))")

' variable range
Sheet2.Range("A2").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR($A$2:$A$" & lLR & ")=2021), --($B$2:$B$" & lLR & "))")

End Sub

**abhi_jain80** · 06-19-2021, 05:00 PM

Originally Posted by TMS

One way:

Option Explicit

Sub sTotal2021()

Dim lLR As Long
lLR = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

' fixed range
Sheet2.Range("A1").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR($A$2:$A$984)=2021), --($B$2:$B$984))")

' variable range
Sheet2.Range("A2").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR($A$2:$A$" & lLR & ")=2021), --($B$2:$B$" & lLR & "))")

End Sub

Thanks, this worked for me

**TMS** · 06-19-2021, 05:11 PM

You're welcome. Thanks for the rep.

**abhi_jain80** · 06-21-2021, 04:44 AM

Hi TMS,

Sorry for coming back to you on the same query again.

Just wondering what would be the formula if I need to sum the values between the dates, say from 31/07/2021 to 31/12/2021 for the variable range of data?

Many Thanks,
Abhi

**TMS** · 06-21-2021, 05:47 AM

Not sure why youwant this in VBA; you could just use the formulae directly.

Whatever

Option Explicit

Sub sTotal2021()

Dim lLR As Long
lLR = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

' Subtotal year 2021
'=SUMPRODUCT( --(YEAR(Sheet1!$A$2:$A$984)=2021), --(Sheet1!$B$2:$B$984))

' fixed range
Sheet2.Range("A1").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR(Sheet1!$A$2:$A$984)=2021), --(Sheet1!$B$2:$B$984))")

' variable range
Sheet2.Range("A2").Value = _
    Evaluate("=SUMPRODUCT( --(YEAR(Sheet1!$A$2:$A$" & lLR & ")=2021), --(Sheet1!$B$2:$B$" & lLR & "))")

' Subtotal between dates
'=SUMIFS(Sheet1!$B$2:$B$984, Sheet1!$A$2:$A$984,">="&DATE(2021,7,31), Sheet1!$A$2:$A$984,"<="&DATE(2021,12,31))

' fixed range
Sheet2.Range("A3").Value = _
    Evaluate("=SUMIFS(Sheet1!$B$2:$B$984, Sheet1!$A$2:$A$984,"">=""&DATE(2021,7,31), Sheet1!$A$2:$A$984,""<=""&DATE(2021,12,31))")

' variable range
Sheet2.Range("A4").Value = _
    Evaluate("=SUMIFS(Sheet1!$B$2:$B$" & lLR & ", Sheet1!$A$2:$A$" & lLR & ","">=""&DATE(2021,7,31), Sheet1!$A$2:$A$" & lLR & ",""<=""&DATE(2021,12,31))")

End Sub

**abhi_jain80** · 06-21-2021, 12:16 PM

Thank you TMS, this helps

**TMS** · 06-21-2021, 12:26 PM

You're welcome.

vba code to sum the values of a column by year

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Re: vba code to sum the values of a column by year

Thread Information

Users Browsing this Thread

Similar Threads

Getting current year/month/day values and inserting them into macro code

[SOLVED] macro code for comparing month-year then add next column value

VBA code to search through column and allocate to year

Year values in the column

Sum values for month and year where cells in column contain word

[SOLVED] Count values in column, paste current year in another column that many times

Sorting Column Values (Ascending Year)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions