need a formula....help!!!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: need a formula....help!!!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Basicly the spread sheet is a log that we must complete daily. On the sheet we must enter the amount of minutes we work on each project in column C and the code that it falls under in row D (which can be found on the bottom of the page). Then total them all up by code for the day by minutes and enter them in the correct code total box at the bottom of the page. Is there any way to write a formula that will automatically total the minutes for any row that falls under the column C and has the designated code id # (1-7) in column D and place it in the correct totals box at the bottom of the page?

    I have been sitting at my desk for 2 days.....Any info would be great...We are not responsible to put the start time and end time...



    [ This Message was edited by: nickb on 2002-03-14 15:57 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-14 15:44, nickb wrote:
    Basicly the spread sheet is a log that we must complete daily. On the sheet we must enter the amount of minutes we work on each project in column C and the code that it falls under in row D (which can be found on the bottom of the page). Then total them all up by code for the day by minutes and enter them in the correct code total box at the bottom of the page. Is there any way to write a formula that will automatically total the minutes for any row that falls under the column C and has the designated code id # (1-7) in column D and place it in the correct totals box at the bottom of the page?

    I have been sitting at my desk for 2 days.....Any info would be great...I am including an attachment of the spreadsheet that has a day of minutes and what it must look like.
    We are not responsible to put the start time and end time...

    I don't see any attachment.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry...I just copied that from a email to a friend ...can we attach excel files?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nope (regarding attachments). Try a =sumif function. Go through the function wizard (click the old fx button). Criteria in D, values to sum in C.

    Or do subtotals. Click Data->Subtotals... Sum by each change in code (d). Might need to sort your data first...

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-14 16:01 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-14 15:54, nickb wrote:
    Sorry...I just copied that from a email to a friend ...can we attach excel files?
    I believe it is as yet not possible.

    You could perhaps give a sample of 5 entries from C along with the cell address where you want the total.


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    would this do what you want:
    =SUMIF(B1:B11,1,A1:A11)/60
    you'd have to put this formula in each of your code total boxes (i assume there are 7 of these, one for each code total). the formula above will work for code total box #1, but you'd have to change the '1' to a '2' for code total box 2 and so on. the /60 gives the number of hours, but as a fraction.
    does that help?
    oops. that first formula should be
    =SUMIF(D1:D11,1,C1:C11)/60

    [ This Message was edited by: anno on 2002-03-14 16:57 ]

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If D is the criteria range and C is the data in minutes and you want totals in minutes

    =SUMIF(d1:d30000,1,c1:c30000)
    =SUMIF(d1:d30000,2,c1:c30000)
    =SUMIF(d1:d30000,3,c1:c30000)

    etc....Oughta win the battle.

    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-14 16:41 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I do this frequently, but I use a slightly different method than the one used in the other replies. First assign range names to range of minutes and range of codes. This makes your formula a little easier to understand and you dont have to worry about assigning absolute values to the cell references. One caveat though, whether you use my method or one of the others suggested, the both ranges must start and end on the same row. This is just one of the rules for this type of "array" formula. For more info, Mr. Excel has a section in the tip of "Tip of the Week Archive" called CSE Formulas that discuss this type of formula. Anyways, back to matters at hand. After assigning the range names, I would list all the unique codes vertically. If this list started on cell A100 for instance the formula would look like =sum((codenames=A100)*(minutes)) where codenames is the range name assigned to the code values and minutes is the range name assigned to the minute values. Please note that for the formula to work properly you must press (Ctrl)(Shift()Enter) simlutaneously instead of just (Enter) to enter the formula. Then copy the formula down as far as your list extends. Since the formula refers to the codes listed to the left instead of coding them into the formula, you can change your code system and all you have to do is modify your list instead of editing all the formulas. Refer to Mr. Excel's CSE article for all the skinny. These formulas are very powerful once you learn how to use them, but beware, they are computationally intensive so if you have a lot of them, it can really slow down the reclac time on your spreadsheets. Good luck.

    [ This Message was edited by: 2077delta on 2002-03-14 17:26 ]

    [ This Message was edited by: 2077delta on 2002-03-14 17:27 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO you are a GOD!!!!!

    Bro you have no idea has satisfied I am. Thank you for taking the tim eto help with my problem....Board Master is an understatement..

    thanks..

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Nick, I like your attitude!

    Glad to be of assistance, all's well that ends well.

    Happy St. Patty's!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com