Excel formula question

[J15491]

I have the following formula in a database worksheet cell, =SUMIFS($N$2:$N$1048571,$T$2:$T$1048571,$AD2,$S$2:$S$1048571,"Utilities"). It works in my worksheet(Sheet2) and populates the data to my Dashboard. My problem is this: When I start a new month erasing all the data in my user form database, the values in sheet 2 are cleared. As a result Cells J17, M17 and N17 show !#Ref because cells AC2, AE2 and AF2 are now empty. I'm not sure how to fix this. I'm also wondering how to code the above formula in excel vba.

I tried this, but it didn't work



Sub Formula_BudgetTotals()

'Assigns hard code formula to a single cell.

Range("AC2").Formula = "=SUMIFS($N$2:$N$1048571,$T$2:$T$1048571,$AD2,$S$2:$S$1048571,"Utilities")"

End Sub



Hope I explained it well enough.

 

[Michael M]

"erasing all the data "
Are you delting the rows or clearing just the cells ??
How are you clearing the data ?? manually or via VBA ?
And why are your formula extending to over 1 million rows ?

 

[Alex Blakenburg]

I agree with Michael that setting a formula to such a large data range is not doing the spreadsheets performance any favours.
But in terms of getting your code working just put double quotes around your string variable eg ""Utilities""

Sub Formula_BudgetTotals()

    'Assigns hard code formula to a single cell.
   
    Range("AC2").Formula = "=SUMIFS($N$2:$N$1048571,$T$2:$T$1048571,$AD2,$S$2:$S$1048571,""Utilities"")"

End Sub

 

[J15491]

"erasing all the data "
Are you delting the rows or clearing just the cells ??
How are you clearing the data ?? manually or via VBA ?
And why are your formula extending to over 1 million rows ?

Actually I currently delete the data entered in the userform database with a command button by highlighting each row of data and clicking delete. I plan to add a command button that will erase all rows of data from the database with one click. I just need to work out the code for that. The current delete button is used to delete one row of data. As far as the formula goes I planned to extend it to just 50 rows. The data is entered into the text boxes on the userform and then populated to the appropriate cells in sheet2.

 

[Michael M]

Maybe post the code you are using at the moment AND what else you want done with it !!