Previous Topic Next Topic

Total and Avg. data from another worksheet based on day of week

Closed
mikeexcelscrub
Posts
1
Registration date
Monday June 16, 2014
Status
Member
Last seen
June 16, 2014
 - Jun 16, 2014 at 02:49 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Jun 17, 2014 at 11:31 AM
I am doing tracking for my employer. I have a sheet (Sheet 1) that I input data for every day of that month(i.e. 1st-31st) in the adjacent column I listed what day of the week it was i.e. MON, TUE, WED. The data inputed is the # of activities we did, with each activity being a column . Examples of the activities would be Policy Delivered, Appointment Cancelled. There are over a dozen columns. I would like to be able to have this new sheet (Sheet 2) pull the values from Sheet 1 based on what the day of the week it was and add up the values for each activity. This would tell us what are total business was for each work day, such as in the month of May we delivered 36 policies on Tuesdays. Sheet 3 I would like for it to be able to average the values based on how many of that specific day of the week there was in the month, for example on average in May we delivered 9 policies every Tuesday. Is what I am asking even possible thanks for any help in advance.

1 reply

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Jun 17, 2014 at 11:31 AM
Hi Mike,

1) adding values based on day of the week.
* Select the cell you want to put the result in, run the code below.
* The code will ask you to input the day of the week.
* The code will give you the opportunity to cancel in case you forgot to select the correct destination cell.

Here is the code: 

Sub RunMe()
Dim lCol, MyCol As Integer
Dim lRow, Result As Long
Dim sValue As String
Dim Check As Variant

sValue = InputBox("For which day would you like to add the activities?:")

Sheets("Sheet1").Select
lRow = Range("B1").End(xlDown).Row

For Each cell In Range("B2:B" & lRow)
    If cell.Value = sValue Then
        lCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column
        MyCol = 2
        
        Do
            MyCol = MyCol + 1
            Result = Result + Cells(cell.Row, MyCol).Value
        Loop Until lCol = MyCol
        
    End If
Next cell

Check = MsgBox("The result will be placed in the active cell.", vbOKCancel)
If Check = vbCancel Then Exit Sub
ActiveCell = Result

End Sub


2) Obtaining average.
Use formula for that (assuming you placed result in A1 of sheet2):
=Sheet2!A1/COUNTIF(B2:B32,"MON")

Best regards,
Trowa
0