# Sumifs with date range in vba

#### achu

##### Member
Dear expart,
kindly help me with this function ,

i have puting formula with sumifs with date range select

kindly solve this formula,

Code:
``````Sub Galaxy_payment()
Const externalwbk As String = "Vinit-Master Payment Sheet.xlsx"
Const externalwsht As String = "Data"

Dim inputrange As Range
Dim tablename As String
Dim pathname As String
Dim mydate As Date

mydate = DateSerial("\$D\$16", "\$C17", "\$B17")

pathname = ThisWorkbook.path
If Right(pathname, 1) <> "\" Then pathname = pathname & "\"
Set inputrange1 = Sheets("Report").Range("D17:D28")

With Workbooks(externalwbk).Worksheets(externalwsht)
lastrow = .Range("A2").End(xlDown).Row
If .ListObjects.Count > 0 Then
tablename = .ListObjects(1).Name

End If
End If

inputrange1.Formula = "=Sumifs(" & "'" & pathname & "[" & externalwbk & "]" & externalwsht & "'" & "!" & "\$AQ\$2:\$AQ\$" & lastrow & "," & _
"'" & pathname & "[" & externalwbk & "]" & externalwsht & "'" & "!" & "\$C\$2:\$C\$" & lastrow & "," & "\$C\$4" & "," & _
"'" & pathname & "[" & externalwbk & "]" & externalwsht & "'" & "!" & "\$F\$2:\$F\$" & lastrow & "," & mydate & ")"

End With

End Sub``````

Last edited by a moderator:
Achu,

Pleae find attached file

#### Attachments

• Book1 ANK.xlsx
819.4 KB · Views: 8
Somebody help me this formula...

Hi ,

What is the connection between the file you have uploaded and the code you have posted ?

What is it that you want done ? Explain accurately , and if possible , enter the formula in the appropriate cell. This formula can then be developed using VBA.

Narayan

Dear sir,

i have column 5
year 2010
Year 2011
Year 2012
Year 2013
Year 2014
and row 12
Jan 1
Feb 2
Mar 3
Apr 4
May 5
June 6
Jul 7
Aug 8
Sept 9
Oct 10
Nov 11
Dec 12

if Year 2010 = C1
Month jan = B2
and A2 = 1
A3 = 2
.
.
.
.
A13 = 12

then my formula is automati
c calculation year and month wise...

i want auto calculat no Year and month wise with formula = Dateserial(C\$1,\$B2,\$A2)

i am not able to add dateserial function in sumifs as per above

thankyou ..

Narayan Sir.

Hi ,

I am still not sure about what you want to do.

However , if you see the Excel VBA help on the DateSerial function , it says the syntax of the function is :

DateSerial(year, month, day)

where all the 3 parameters are integers.

If your year is stored in cell C1 , month in cell B2 , and the day in cell A2 , then the function would work , provided C1 , B2 and A2 all have integer values.

Your post says that B2 contains the text value Jan.

With this value in B2 , the DateSerial function will not work.

Narayan

Dear sir,

sheet1 = Data
Sheet 2 - data table as i want

kindly put formula in vba

#### Attachments

• Book1 ANK.xlsx
818.9 KB · Views: 8
Hi ,

See if this is acceptable.

Narayan

#### Attachments

• Book1 ANK (1).xlsm
817.3 KB · Views: 24
Thank You Narayan Sir,

it is superb.......

again huge thank you this help

here is i am not able to put formula dateserial function because i want that

ok i got

This example is perfect, is there a way to input the result in the cells, instead of the formulas ?

Yes, just replace formulas with values …​