3

I'm trying to set up a sustainable budget excel workbook that I can easily set up the sheets to be named the different categories that I'd like in the summary. The idea is to add the items from the receipts to the different sheet, within their specific category. I'd like to be able to have a formula that separates the dates, and text within the sheet to just get the sum of the numbers within that sheet. Is there an easy way to set up the isnumber formula to exclude dates?

  • 1
    Unfortunately, Excel sees dates as numbers with special formatting. That's going to make it tricky. If all your currency values are less than a certain value, you can use SUMIF. For instance, 01JAN2000 is 36,526. If all your dates are after that and all the values you want to sum are less than that, you can use =SUMIF([range],"<36526") – Engineer Toast Dec 17 '15 at 3:29
1

Consider the following UDF():

Public Function SuperSum(shName As String) As Variant
   Application.Volatile
   Dim sh As Worksheet, r As Range, v As Variant
   Set sh = Sheets(shName)
   For Each r In sh.UsedRange
      v = r.Value
      If IsNumeric(v) And Not IsDate(v) Then
         SuperSum = SuperSum + v
      End If
   Next r
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=SuperSum("Sheet2")

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.