Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having trouble finding the right function | New Users to Excel | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Help Needed with Finding a Function | New Users to Excel | |||
function for finding a value! | Excel Worksheet Functions | |||
Finding a function in Excel | Excel Worksheet Functions |