| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif for dddd format
Need to count a row if the date formatted as dddd is X (Monday). imported
date format is 1/10/2009 8:46:00 AM and then formated. On another sheet i need to count how many times a row has "Friday". ex. =COUNTIF('US WW 011009'!J:J,"Saturday") then I need to be able to average a result valuse from the first sheet "IF" a valuse of Friday is in the row. So the columns contain the result of a simple =H4-G4. I then need to averge the I:I IF J:J = Friday. Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif for dddd format
=SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Saturday")*1)
but this is a bit cleaner: =SUMPRODUCT((WEEKDAY('US WW 011009'!J1:J20)=7)*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... Need to count a row if the date formatted as dddd is X (Monday). imported date format is 1/10/2009 8:46:00 AM and then formated. On another sheet i need to count how many times a row has "Friday". ex. =COUNTIF('US WW 011009'!J:J,"Saturday") then I need to be able to average a result valuse from the first sheet "IF" a valuse of Friday is in the row. So the columns contain the result of a simple =H4-G4. I then need to averge the I:I IF J:J = Friday. Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif for dddd format
that worked great, thank you..
do you have any ideas on how to average a value from the sheet with those selected rows? In other words jobs that were completed on friday took X time to complete and that value is the result of a formula on that row. So now that I know how many jobs were completed on Friday I want to know the average time it took for the jobs. Make sense? "Bernie Deitrick" wrote: =SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Saturday")*1) but this is a bit cleaner: =SUMPRODUCT((WEEKDAY('US WW 011009'!J1:J20)=7)*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... Need to count a row if the date formatted as dddd is X (Monday). imported date format is 1/10/2009 8:46:00 AM and then formated. On another sheet i need to count how many times a row has "Friday". ex. =COUNTIF('US WW 011009'!J:J,"Saturday") then I need to be able to average a result valuse from the first sheet "IF" a valuse of Friday is in the row. So the columns contain the result of a simple =H4-G4. I then need to averge the I:I IF J:J = Friday. Any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif for dddd format
In general, something like this, perhaps, with duration in column K:
=SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Friday")*'US WW 011009'!K1:K20)/SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Friday")*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... that worked great, thank you.. do you have any ideas on how to average a value from the sheet with those selected rows? In other words jobs that were completed on friday took X time to complete and that value is the result of a formula on that row. So now that I know how many jobs were completed on Friday I want to know the average time it took for the jobs. Make sense? "Bernie Deitrick" wrote: =SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Saturday")*1) but this is a bit cleaner: =SUMPRODUCT((WEEKDAY('US WW 011009'!J1:J20)=7)*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... Need to count a row if the date formatted as dddd is X (Monday). imported date format is 1/10/2009 8:46:00 AM and then formated. On another sheet i need to count how many times a row has "Friday". ex. =COUNTIF('US WW 011009'!J:J,"Saturday") then I need to be able to average a result valuse from the first sheet "IF" a valuse of Friday is in the row. So the columns contain the result of a simple =H4-G4. I then need to averge the I:I IF J:J = Friday. Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif for dddd format
Awesome,
I didn't even think about going that way. Thank you very much "Bernie Deitrick" wrote: In general, something like this, perhaps, with duration in column K: =SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Friday")*'US WW 011009'!K1:K20)/SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Friday")*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... that worked great, thank you.. do you have any ideas on how to average a value from the sheet with those selected rows? In other words jobs that were completed on friday took X time to complete and that value is the result of a formula on that row. So now that I know how many jobs were completed on Friday I want to know the average time it took for the jobs. Make sense? "Bernie Deitrick" wrote: =SUMPRODUCT((TEXT('US WW 011009'!J1:J20,"dddd")="Saturday")*1) but this is a bit cleaner: =SUMPRODUCT((WEEKDAY('US WW 011009'!J1:J20)=7)*1) HTH, Bernie MS Excel MVP "smcmoran" wrote in message ... Need to count a row if the date formatted as dddd is X (Monday). imported date format is 1/10/2009 8:46:00 AM and then formated. On another sheet i need to count how many times a row has "Friday". ex. =COUNTIF('US WW 011009'!J:J,"Saturday") then I need to be able to average a result valuse from the first sheet "IF" a valuse of Friday is in the row. So the columns contain the result of a simple =H4-G4. I then need to averge the I:I IF J:J = Friday. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =text(a1,"dddd") question | Excel Worksheet Functions | |||
| Conditional Formatting =IF($B10, TEXT("dddd"))? | Excel Worksheet Functions | |||
| Conditional format using dddd | Excel Discussion (Misc queries) | |||
| COUNTIF with non-zero format? | Excel Discussion (Misc queries) | |||
| Are you kidding me??? Are you telling me I can't sort format: dddd, mmmm dd, yyyy? | Excel Discussion (Misc queries) | |||
Linear Mode
