Countif for dddd format

S

smcmoran

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?
 
B

Bernie Deitrick

=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
 
S

smcmoran

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?
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top