Excel 2007 all MS updates
I need to sum a group of rows based upon the day of the week. Column A
contains the DATE (mm/dd/ccyy), Column B could contain the day of the week
(Sunday, Monday etc.) and Column C would contain the data. Let's say Column
C is the number of students in class on a particular day of the week. Since
classes can meet eratically, I'd like to determine the following based upon
EACH WEEK or EACH MONTH.
Date WeekDay #Students
01/31/10 Sun 12
02/01/10 Mon 30
02/03/10 Wed 32
02/05/10 Fri 26
02/06/10 Sat 15
etc...
1) I'd like to total the number of students from Monday through Friday, but
keep in mind that sometimes there are holidays on Monday and no classes on
certain days of the week depending on semester/quarter. See the above that
doesn't have any Tuesday (purposely not included).
2) Over a range of rows, I'd like to determine the number of students who
attended on each DAY of the week (Sunday, Monday etc.).
Once the above logic is obtained, I can do the MAX, MIN, MEANs, MODE etc.
It's the logic on the missing days etc. that I need help with. All rows
will be used but not all days of the week will be used in any range.
TIA

put in row 1 of col d and copy down.
=SUMPRODUCT((DAY($A$2:$A$22)=ROW())*$C$2:$C$22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
Excel 2007 all MS updates
I need to sum a group of rows based upon the day of the week. Column A
contains the DATE (mm/dd/ccyy), Column B could contain the day of the week
(Sunday, Monday etc.) and Column C would contain the data. Let's say
Column
C is the number of students in class on a particular day of the week.
Since
classes can meet eratically, I'd like to determine the following based
upon
EACH WEEK or EACH MONTH.
Date WeekDay #Students
01/31/10 Sun 12
02/01/10 Mon 30
02/03/10 Wed 32
02/05/10 Fri 26
02/06/10 Sat 15
etc...
1) I'd like to total the number of students from Monday through Friday,
but
keep in mind that sometimes there are holidays on Monday and no classes on
certain days of the week depending on semester/quarter. See the above
that
doesn't have any Tuesday (purposely not included).
2) Over a range of rows, I'd like to determine the number of students who
attended on each DAY of the week (Sunday, Monday etc.).
Once the above logic is obtained, I can do the MAX, MIN, MEANs, MODE etc.
It's the logic on the missing days etc. that I need help with. All rows
will be used but not all days of the week will be used in any range.
TIA

Excel 2007, PivotTable
Daily, Weekly, Monthly
Sum, Avg, Min, Max
All without formulas.
http://www.mediafire.com/file/omz3mmzqnmy/02_07_10.xlsx