Conditional Summing

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

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?
 
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?
 
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.
 
Back
Top