EXCEL-VBA-TIPS
EXCEL-VBA-TIPS
Custom VBA macro to Sum and Count EXCEL has functions to sum or count the values in a range of data. To understand how these functions work, this tip presents a code to sum and count the values in a column of data. The companion spreadsheet shows an example dataset and the macros used to make the calculations. The VBA coding shown below contains two example macros to sum the data, a macro to sum only data that satisfies a given condition, a macro to count the number of data values, and a macro that combines counting and summing so that the average can be computed:
Sub SumX() ' This sub sums the values in range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables sum_val = 0 ' Loop over all the values and sum them For ctr = 1 To 4
x = Cells(ctr, 1).Value ' Update the summation sum_val = sum_val + x Next ' Output the summation Range("C1").Value = sum_val End Sub
Sub SumX2() ' This sub sums the values in range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables sum_val = 0 rctr = 1 ' Loop over all the values and sum them Do Until Cells(rctr, 1).Value = ""
x = Cells(rctr, 1).Value ' Update the summation sum_val = sum_val + x ' Update the row counter rctr = rctr + 1 Loop ' Output the summation Range("C1").Value = sum_val End Sub
Sub CountX() ' This sub counts the values in range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables cnt_val = 0 rctr = 1 ' Loop over all the values and count them Do Until Cells(rctr, 1).Value = ""
' Update the count cnt_val = cnt_val + 1 ' Update the row counter rctr = rctr + 1 Loop ' Output the number of values Range("C2").Value = cnt_val End Sub
Sub AvgX() ' This sub sums and counts the values in range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables sum_val = 0 cnt_val = 0 rctr = 1 ' Loop over all the values and sum and count them Do Until Cells(rctr, 1).Value = ""
x = Cells(rctr, 1).Value ' Update the summation sum_val = sum_val + x ' Update the count cnt_val = cnt_val + 1 ' Update the row counter rctr = rctr + 1 Loop ' Compute the average If cnt_val > 0 Then avg_val = sum_val / cnt_val Else avg_val = "undefined" End If ' Output the average Range("C3").Value = avg_val
End Sub
Sub CondSumX() ' This sub sums the values in range A1:A4 ' that are greater than a threshold value ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables threshold = 2 sum_val = 0 ' Loop over all the values and sum them For ctr = 1 To 4
x = Cells(ctr, 1).Value ' Check to see if the value of x is above the threshold If x > threshold Then
' Update the summation sum_val = sum_val + x End If Next ' Output the summation Range("C4").Value = sum_val End Sub
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel vba tips
- excel vba for loop dynamic range
- complete excel vba secrets tips for professionals
- excel vba find row number of matching value
- vba4 using cell formulas in vba performing the same
- excel macro record and vba editor
- excel vba notes for professionals
- automation of 1dcutx in excel by vba
- excel vba select last row with value
- learn vba for excel
Related searches
- excel vba userform listbox multiselect
- free excel vba code samples
- excel vba commands
- excel vba code library
- excel vba quick reference pdf
- excel vba listbox multiple selection
- excel vba character code
- excel vba guide pdf
- excel vba reference
- excel vba reference library
- excel vba listbox selected value
- excel vba cheat sheet