Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Summing
I'm new at this so be kind please.
I have a spreadsheet that has five columns. The data in the columns is based on a persons name and changes with each person selected from a drop down list. One column list the numbers of hours worked on a workorder in a second column. a third column has the dat the hours were worked. I can get this for the entire year. What i want is to be able to total the hours in a given week of the year. I used the Conditional Sum Wizard and it works great with exact dates and times but will not work with a range of dates. With me so far? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Summing
Hi Tom ...Yes, I'm with you so far.
Do you want the spreadsheet to record the total for a week, or is just an ad hoc enquiry that you might want to occasionally perform? (ie, do you need a cell for the result?) If you just want to perform an ad hoc calc, then you might try using the Worksheet_BeforeDoubleClick or Worksheet_BeforeRightClick event to trigger a macro to: (1) calculate the cell address of the doubleclick and then test to see what day of the week it corresponds to (2) find the next Friday my moving right and performing a weekday() test (3) one you find Friday, run a little loop that looks back 5 days (it might have to look back at every 5th column) and do a quick addition (4) pop up a msgbox() with the result If you want to record the weekly total in a column, you need to do the same, and add a column after the friday of each week (however, you might run out of columns unless your running Excel 2007). With me so far? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Summing
Hi Greg, I think I understand what your saying.
I think I did a bad job explaining my situation. I was in a hurry. I use Excel 2003 and Query to query a database of operators, their time and workorders with the date the work was done. The query is determined by selecting a persons name from a drop down list and comparing that name to the names in the data base. The query then pulls all the records by that name and orders them by the date. I then have a sum that adds ALL the hours in the hours column and gives me a running total for the year. What I want next is to sum only the hours that pertain to a given week. I can make this happen on an individual basis but if I then select a different name, the data all changes and the conditional sum returns erroneous information. I've tried WEEKNUM and SUMIF and other logical functions but I know I'm missing something simple. I'm new at this programming stuff and learning on the fly with no training. I only recently discovered the wonderful world of VLOOKUP and HLOOKUP. In short I'd like to be able to examine a column of dates, determine what week each date is in and then sum the hours for that week only. Thanks for the feedback. "Greg Glynn" wrote in message oups.com... Hi Tom ...Yes, I'm with you so far. Do you want the spreadsheet to record the total for a week, or is just an ad hoc enquiry that you might want to occasionally perform? (ie, do you need a cell for the result?) If you just want to perform an ad hoc calc, then you might try using the Worksheet_BeforeDoubleClick or Worksheet_BeforeRightClick event to trigger a macro to: (1) calculate the cell address of the doubleclick and then test to see what day of the week it corresponds to (2) find the next Friday my moving right and performing a weekday() test (3) one you find Friday, run a little loop that looks back 5 days (it might have to look back at every 5th column) and do a quick addition (4) pop up a msgbox() with the result If you want to record the weekly total in a column, you need to do the same, and add a column after the friday of each week (however, you might run out of columns unless your running Excel 2007). With me so far? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Summing | Excel Discussion (Misc queries) | |||
Conditional Summing | Excel Worksheet Functions | |||
CONDITIONAL SUMMING | Excel Discussion (Misc queries) | |||
Conditional Summing | Excel Worksheet Functions | |||
Conditional SUMMING | Excel Programming |