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?

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?

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?

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?

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?