Sum data found in a range or specific data

This function lets someone take the sum of data in a row that matches a range of data so you don't have to do a sumif() function over and over for the sum of the data you want.

=sumproduct((m4:zz4>=(I8-120))*(m4:zz4<=(I8)),m8:zz8) =sumproduct((row of dates>=(date-120 days"past date"))*(row of dates<=(date"future date")),row to be added)

This function allows you to count multiple data types instead of using a sumif function.

=sumproduct((m4:zz4>=(I8-120))*(m4:zz4<=(I8)),m8:zz8) =sumproduct((row of dates>=(date-120 days"past date"))*(row of dates<=(date"future date"))*(Other Data Typer Row = Unique datatype),row to be added)

Count the Number of Colored Cells in Excel

First open the workbook in which you wish to count or sum cells by a fill color. Now go into Visual Basic. Insert new module:

Code:

Funtion ColorCount(rColor As Range, rRange as Range, Optional Sum As Boolean)
Dim rCell as Range
Dim lCol as Range
Dim vResult


1Col = rColor.Interior.ColorIndex

If Sum = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

Else

For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

End If

COlorFunction = vResult

End Function

--------------------------------------------------------

You can use the function that was just created by inputing this code into a cell

=ColorFunction($C$1,$A$1:$A$12,TRUE) to sum the values in the A:A column Range that have the same fill color as cell $C$1. The reason it will sum in because the code will have True as the last argument for the custom function.

VBA Code for Random Generation of Stats

CoinToss()

Dim x As Integer
Dim Row as Integer
Dim NumberOfFunds As Integer

erOfFunds = Sheets("Sheet1").Range("NumberOfPeople")

Worksheets("Sheet1").Range("ClearNumber").Select
Selection.ClearContents----------------------------Clears data from cells

For x = 1 To NumberOfFunds-------------Starts loop

Application.ScreenUpdating = False--------------Stops flickering of screen

Calculate-------------------------------This calculates the random generation function
Range("Future1").Select
Selection.copy
Range("Option1").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkpBlanks _
:=False, Transpose:=False

Range("Future2").Select
Selection.copy
Range("Option2").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next

Range("M10:N109").Select----------------------This formats the numbers that are generated
Application.CutCopyMode = False
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"

Range("A1").Select------------------ends macro in cell a1

Sub

Find Function in VBA

----------This Search Feature allows you to add a variable such as strMyString------------
----------strMyString can be any value you want to search for in a worksheet-------------

Dim strMyString as String----------Sets the variable for use

Cells.Find(What:=strMyString, After:=Activecell, Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Basic Setup for VBA Code:

Sub Data_Filter()

Dim x as Integer-------------These are Variables
Dim Y as String--------------These are Variables
Application.ScreenUpdating = False-----------This Stops the screen from Flickering
On Error GoTo EndOfSection-------------This keeps your macro running when there is a error

For x = 1 to NumberofTrainings-------Start of a loop
Sheets("Sheet1").Select------------Selecting a Worksheet
Range("C2").Offset(x, 0).Select----------Selecting a range and then moving from that range
Y = Activecell------------------Telling a Variable what its value is
Sheets.Add.Name = NameofTab------------Creating a new worksheet
Range (ActiveCell.EntireRow.Address) (1, 1).Select----------Moving activecell to the beginning of a row

Next------------------------End of a loop

EndOfSection:-------------Where code breaks to when a error occurs

End Sub---------------Ends the macro