Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi gang,
    Sheet1 has Names of people in column A X Days of the Year in Row 1. Activity codes (i.e. "V") are entered for everyone for each day.
    Sheet2 has Names of people in column A (from sheet 1) X Date of every Monday in Row 1.
    I need to count the number of times Activity "V" appears in Sheet1 for the week that starts on Monday (date in Row 1), for every individual and of course be able to copy it accross and down.
    Any ideas?
    tks in advance
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In B2 on Sheet2 enter the following formula:

    =SUMPRODUCT((Sheet1!$B$1:$AF$1-WEEKDAY(Sheet1!$B$1:$AF$1,3)=B$1)*(Sheet1!$B2:$AF2 ="V"))

    Change the end column AF to the column letters for the last column you use on Sheet1.
    Fill down, then right as far as needed.

    See the attached sample workbook: [attachment=86566:CountifWeekdays.xls]
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Absolutely perfect! Exactly what I needed.
    Thank you so much. From now on I have a perpetual calendar for tracking availabilities and vacation time.
    Thanks again.
    Johanne Champagne
    Montreal (Quebec) CANADA

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok. Let's push this a little more.
    I have the same kind of calendar sheet for assistants.
    In addition to adding up the "Vs" I want to put a conditional formatting if the assistant is on vacation as well (i.e. has a "V" that week also).
    See the attached sample.
    Attached Files Attached Files
    Johanne Champagne
    Montreal (Quebec) CANADA

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just realized this won't work. Can't reference to other worksheet in a conditional formatting.
    I will need to figure out something else.
    Johanne Champagne
    Montreal (Quebec) CANADA

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can refer to cells on another sheet in conditional formatting by using defined names.

    See the attached version.

    [attachment=86579:TestCountifWeekdays.zip]

    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Something is not working. Cell C5 in the Vacation should be shaded as well. Barbara is on vacation that week. It should be shaded even if Ringo is not on vacation.
    Also, the names in the Assistant sheet are not in the same order as in the Vacation sheet. In the file I am working on, one assistant works for more than one guy. The corresponding cell should be shaded for both guys she works with.
    One other question: you added this last part to the formula *(Guys!$A$3:$A$6=$A3) that was not there on your first answer. If it would be possible for you to explain please.
    tks
    Johanne Champagne
    Montreal (Quebec) CANADA

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Oops, sorry. I made a mistake in the definition of the name AssistantVacation.

    While Vacation!C3 is the active cell, the name AssistantVacation should refer to the formula

    =SUMPRODUCT((Assistant!$B$2:$IV$2-WEEKDAY(Assistant!$B$2:$IV$2,3)=Vacation!C$2)*(Ass istant!$B$3:$IV$6="V")*(Assistant!$A$3:$A$6=Vacati on!$B3))

    I had inadvertently left some of the references to the Assistant sheet relative instead of absolute.

    The addition of *(Guys!$A$3:$A$6=$A3) in the cell formula for C3 is not the only change, I also expanded the range in the middle part. It now is (Guys!$C$3:$IV$6="V") instead ofg (Guys!$C3:$IV3="V").

    This allows the order of the names on the Vacation sheet to be different from that on the Guys sheet. The defined name works similarly.

    I have attached a corrected version of the workbook.

    [attachment=86583:TestCountifWeekdays.zip]

    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It works perfectly and I totally get it now. In the time between I asked you the question and your answer I had already changed my mind about what exactly I wanted to do and I was able to adapt the formula.
    I just love Excel and Woody's Lounge and you.
    Thanks a million.
    jc
    Johanne Champagne
    Montreal (Quebec) CANADA

  10. #10
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. Me again. I needed to have 3 conditional formatting for one cell. It works just fine except that my screen refresh is so slow it's almost impossible to move around in the file. I put the recalculation at manual and it did not do anything.
    Any else I can turn off?
    Johanne Champagne
    Montreal (Quebec) CANADA

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Take a look at the attached version. I used additional defined names to make the formulas look at the appropriate week only instead of the entire date range. It should be much more responsive now.

    [attachment=86619:TestCountifWeekdays.zip]

    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes. Much better. Thank you. I am also trying to put an "OR" and an "AND" function in there and can't seem to make it.
    If the assistant is on vacation "V" or sick "S" I want the same color.
    If the assistant is absent for part of the day i.e. a number between 0.5 and 6.75 I want another color.
    I tried to integrate the AND and the OR in the SUMPROD without success.
    Help please.
    Johanne Champagne
    Montreal (Quebec) CANADA

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can change the formula for conditional formatting, e.g.

    =AssistantVacation>0

    becomes

    =OR(AssistantVacation>0,AssistantSick>0)

    See the attached version: [attachment=86637:TestCountifWeekdays.zip]

    I don't know how you indicate the assistant being absent part of the day - the workbook we've discussed until now just uses letters to indicate a day of absence. So I can't answer your second question.
    Attached Files Attached Files

  14. #14
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is just perfect Hans. You pointed me in the right direction.
    I just need to create another Defined Name searching for certain numbers instead of letters.
    I have more questions about hyperlinks but I will start another thread for that one.
    Thanks again.
    Johanne Champagne
    Montreal (Quebec) CANADA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •