Finding the right function & formula
 

I'm trying to find a function & formula that will add all the Volume Total
for specific day of the week in the Date column. For instance, I want add the
sum of all the Monday volume totals and so forth...grouping the volume totals
by day of the week.

Column A Column B
Date Volume Total
Saturday, January 05, 2008 200
Sunday, January 06, 2008 209
Monday, January 07, 2008 231
Tuesday, January 08, 2008 240
Wednesday, January 09, 2008 201
Thursday, January 10, 2008 219
Friday, January 11, 2008 210
Saturday, January 12, 2008 182
Sunday, January 13, 2008 182
Monday, January 14, 2008 227
Tuesday, January 15, 2008 198
Wednesday, January 16, 2008 232
Thursday, January 17, 2008 221
Friday, January 18, 2008 191

--
Thank you. - KT

Finding the right function & formula
 

Assuming your dates are true Excel dates:

=SUMPRODUCT(--(WEEKDAY(A2:A15,2)=n),B2:B15)

Where n = day of the week: 1 = Monday thru 7 = Sunday

--
Biff
Microsoft Excel MVP


"KT" wrote in message
...
I'm trying to find a function & formula that will add all the Volume Total
for specific day of the week in the Date column. For instance, I want add
the
sum of all the Monday volume totals and so forth...grouping the volume
totals
by day of the week.

Column A Column B
Date Volume Total
Saturday, January 05, 2008 200
Sunday, January 06, 2008 209
Monday, January 07, 2008 231
Tuesday, January 08, 2008 240
Wednesday, January 09, 2008 201
Thursday, January 10, 2008 219
Friday, January 11, 2008 210
Saturday, January 12, 2008 182
Sunday, January 13, 2008 182
Monday, January 14, 2008 227
Tuesday, January 15, 2008 198
Wednesday, January 16, 2008 232
Thursday, January 17, 2008 221
Friday, January 18, 2008 191

--
Thank you. - KT

Finding the right function & formula
 

Hi,

You can also use Data Subtotals. But you will have to sort the data in
column A first.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KT" wrote in message
...
I'm trying to find a function & formula that will add all the Volume Total
for specific day of the week in the Date column. For instance, I want add
the
sum of all the Monday volume totals and so forth...grouping the volume
totals
by day of the week.

Column A Column B
Date Volume Total
Saturday, January 05, 2008 200
Sunday, January 06, 2008 209
Monday, January 07, 2008 231
Tuesday, January 08, 2008 240
Wednesday, January 09, 2008 201
Thursday, January 10, 2008 219
Friday, January 11, 2008 210
Saturday, January 12, 2008 182
Sunday, January 13, 2008 182
Monday, January 14, 2008 227
Tuesday, January 15, 2008 198
Wednesday, January 16, 2008 232
Thursday, January 17, 2008 221
Friday, January 18, 2008 191

--
Thank you. - KT

Finding the right function & formula
 

Wonderful! Just curious, what does the ,2 represent in the formula?

Now how do I calculate the Means or Average for each day of the week?
--
Thank you. - KT


"T. Valko" wrote:

Assuming your dates are true Excel dates:

=SUMPRODUCT(--(WEEKDAY(A2:A15,2)=n),B2:B15)

Where n = day of the week: 1 = Monday thru 7 = Sunday

--
Biff
Microsoft Excel MVP


"KT" wrote in message
...
I'm trying to find a function & formula that will add all the Volume Total
for specific day of the week in the Date column. For instance, I want add
the
sum of all the Monday volume totals and so forth...grouping the volume
totals
by day of the week.

Column A Column B
Date Volume Total
Saturday, January 05, 2008 200
Sunday, January 06, 2008 209
Monday, January 07, 2008 231
Tuesday, January 08, 2008 240
Wednesday, January 09, 2008 201
Thursday, January 10, 2008 219
Friday, January 11, 2008 210
Saturday, January 12, 2008 182
Sunday, January 13, 2008 182
Monday, January 14, 2008 227
Tuesday, January 15, 2008 198
Wednesday, January 16, 2008 232
Thursday, January 17, 2008 221
Friday, January 18, 2008 191

--
Thank you. - KT

Finding the right function & formula
 

=SUMPRODUCT(--(WEEKDAY(A2:A15,2)=n),B2:B15)
what does the ,2 represent in the formula?

That determies how the function evaluates the weekdays. There are 3
different arguments available:

1 = weekdays are numbered as 1 = Sunday thru 7 = Saturday
2 = weekdays are numbered as 1 = Monday thru 7 = Sunday
3 = weekdays are numbered as 0 = Monday thru 6 = Sunday

how do I calculate the Means or Average for each day of the week?

Array entered** :

=AVERAGE(IF(WEEKDAY(A2:A15,2)=n,B2:B15))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"KT" wrote in message
...
Wonderful! Just curious, what does the ,2 represent in the formula?

Now how do I calculate the Means or Average for each day of the week?
--
Thank you. - KT


"T. Valko" wrote:

Assuming your dates are true Excel dates:

=SUMPRODUCT(--(WEEKDAY(A2:A15,2)=n),B2:B15)

Where n = day of the week: 1 = Monday thru 7 = Sunday

--
Biff
Microsoft Excel MVP


"KT" wrote in message
...
I'm trying to find a function & formula that will add all the Volume
Total
for specific day of the week in the Date column. For instance, I want
add
the
sum of all the Monday volume totals and so forth...grouping the volume
totals
by day of the week.

Column A Column B
Date Volume Total
Saturday, January 05, 2008 200
Sunday, January 06, 2008 209
Monday, January 07, 2008 231
Tuesday, January 08, 2008 240
Wednesday, January 09, 2008 201
Thursday, January 10, 2008 219
Friday, January 11, 2008 210
Saturday, January 12, 2008 182
Sunday, January 13, 2008 182
Monday, January 14, 2008 227
Tuesday, January 15, 2008 198
Wednesday, January 16, 2008 232
Thursday, January 17, 2008 221
Friday, January 18, 2008 191

--
Thank you. - KT