SUMIFS VARIABLE USAGE

[faisal_0707]

Hi,I am Faisal Nakade working as a Engg professional.Below is my query:

Actually unable to use variable as second argument in SUMIFS function.

If i put like as below,its working:

ActiveCell.FormulaR1C1 = _

"=SUMIFS('Service Affecting'!C[22],'Service Affecting'!C[36],""BTS"",'Service Affecting'!C[6],""RAN"",'Service Affecting'!C[8],"">=December 10, 2012 07:00:00"",'Service Affecting'!C[2],Summary!RC[-1])"

But instead of December 10, 2012 07:00:00 i pass a variable i for date,as it date varies every day,i dont get desired output.kindly guide me if m using proper syntax:

ActiveCell.FormulaR1C1 = _

"=SUMIFS('Service Affecting'!C[22],'Service Affecting'!C[36],""BTS"",'Service Affecting'!C[6],""RAN"",'Service Affecting'!C[8],">="&i,'Service Affecting'!C[2],Summary!RC[-1])"

 

[NARAYANK991]

Hi Faisal ,


I am not sure , but can you try your first formula , with the change that you replace the static date with a variable outside the quotes ; for example , your first VBA statement is :

[pre]

ActiveCell.FormulaR1C1 = "=SUMIFS('Service Affecting'!C[22], 'Service Affecting'!C[36],""BTS"",'Service Affecting'!C[6],""RAN"", 'Service Affecting'!C[8],"">=December 10, 2012 07:00:00"", 'Service Affecting'!C[2],Summary!RC[-1])"
If you remove the static date [b]December 10, 2012 07:00:00[/b] , you will get :

[pre][code]ActiveCell.FormulaR1C1 = "=SUMIFS('Service Affecting'!C[22], 'Service Affecting'!C[36],""BTS"",'Service Affecting'!C[6],""RAN"", 'Service Affecting'!C[8],"">="",'Service Affecting'!C[2], Summary!RC[-1])"

[/pre]
Now , if you concatenate the variable i , you will have :

ActiveCell.FormulaR1C1 = "=SUMIFS('Service Affecting'!C[22], 'Service Affecting'!C[36],""BTS"",'Service Affecting'!C[6],""RAN"", 'Service Affecting'!C[8],"">=""&i,'Service Affecting'!C[2], Summary!RC[-1])"[/code][/pre]
Narayan