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
    Hello again,
    In a spreadsheet I put availability for personnel. V for vacation S for sickday and number of hours (i.e. 3) for personal time taken off.
    I used SUMPRODUCT to add up the number of times the Vs appear for one person and I did the same for the S.
    SUMPRODUCT((Sheet1!$A$3:$A$75=$A3)*(Sheet1!$C$3:$I V$75="V")) or "S" works just fine.
    Now I need to add up (not count) the number of hours that was taken as personal time.
    I tried SUMPRODUCT((Sheet1!$A$3:$A$75=$A3)*(Sheet1!$C$3:$I V$75>0)) but it counts all not empty cells.
    What should I be using instead?
    tks
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =SUMPRODUCT((Sheet1!$A$3:$A$75=$A3)*ISNUMBER(Sheet 1!$C$3:$IV$75))

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Almost. This counts how many times there are numbers.
    What should I use if I want to add them up?
    Johanne Champagne
    Montreal (Quebec) CANADA

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JC@OR View Post
    Almost. This counts how many times there are numbers.
    What should I use if I want to add them up?
    Try this:
    =SUMPRODUCT((Sheet1!$A$3:$A$75=$A3)*ISNUMBER(Sheet 1!$C$3:$IV$75),(Sheet1!$C$3:$IV$75))

    Is that what you are looking for?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get a #VALUE error.
    Johanne Champagne
    Montreal (Quebec) CANADA

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JC@OR View Post
    I get a #VALUE error.
    Hmm.... It works in my spreadsheet. Are you using Excel 2007? I attached a sample spreadsheet that shows what I did. The formula is in Cell C2[attachment=87138:Test SUMPRODUCT.xlsx]
    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
    My mistake.
    I had typed the cell reference wrong.
    Tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I used Bill Wright's attachment and tried this in cell D2 ....
    =SUM(A3:G4)
    and I got the same answer of 8

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Tim Sullivan View Post
    I used Bill Wright's attachment and tried this in cell D2 ....
    =SUM(A3:G4)
    and I got the same answer of 8
    But if the names in column A aren't all the same, you wouldn't get the same result...

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Hans - If I change the name in cell A4 from Smith to Jones I get the same answer of 8.
    Am I missing what JC@OR is trying to accomplish?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Tim Sullivan View Post
    Hi Hans - If I change the name in cell A4 from Smith to Jones I get the same answer of 8.
    Your formula will still return 8, but Bill Wright's formula will return 5 instead of 8, because that formula sums the numeric values in those rows that have the same name as that in A3, because of the condition

    Sheet1!$A$3:$A$75=$A3

  12. #12
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Hans - Good point you made if the only result desired is the name in cell A3.
    Is JC@OR only looking for the result of the name contained in cell A3 or a total for each individual employee?

    Is JC@OR's data layed out as shown by Bill Wright?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    We'll have to wait for her reaction...

  14. #14
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry I did not get back to you sooner.
    This is part of a big spreadsheet that keeps track of availabilities of secretaries to manage for their replacement when they are absent.
    At the same time I want to make sure they don't take too much vacation or too much personal time off.
    The "compilation" sheet on the attached workbook does just that.
    It counts how many vacation days, sick days, or personal time a secretary has taken.
    The #VALUE I got at one point was entirely my fault. The range from Sheet1 and Sheet2 has to be of the same size and I had forgotten to include 2 rows from Sheet2.
    The formula works perfectly now and is exactly what I was looking for.

    Once again I can't thank the guys from the lounge enough for sharing their knowledge with everybody.
    Thanks a million.
    Attached Files Attached Files
    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
  •