Sumifs with date range in vba

[achu]

Dear expart,
kindly help me with this function ,

i have puting formula with sumifs with date range select


kindly solve this formula,

Advanced thank you

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
If .Range("A2:L" & lastrow).Address = .ListObjects(tablename).DataBodyRange.Address Then

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

 

[Ashhu]

Achu,
Can you please upload sample sheet?

 

[achu]

Pleae find attached file

 

[achu]

Somebody help me this formula...

 

[NARAYANK991]

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

 

[achu]

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.

 

[NARAYANK991]

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

 

[achu]

Dear sir,

please find attached file

sheet1 = Data
Sheet 2 - data table as i want


kindly put formula in vba

 

[NARAYANK991]

Hi ,

See if this is acceptable.

Narayan

 

[achu]

Thank You Narayan Sir,


it is superb.......


again huge thank you this help

 

[achu]

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

 

[achu]

ok i got

 

[AFOtest]

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

 

[Marc L]

Yes, just replace formulas with values …​

​