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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 17, 2014 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 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.
Related:
- Total and Avg. data from another worksheet based on day of week
- Transfer data from one excel worksheet to another automatically - Guide
- Total war warhammer 3 free download - Download - Strategy
- Windows calendar show week number - Guide
- Time of day clock stopped - Guide
- Total copy - Download - File management
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jun 17, 2014 at 11:31 AM
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:
2) Obtaining average.
Use formula for that (assuming you placed result in A1 of sheet2):
=Sheet2!A1/COUNTIF(B2:B32,"MON")
Best regards,
Trowa
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