need a formula....help!!!

nickb · Mar 14th, 2002, 04:44 PM

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 ]

Aladin Akyurek · Mar 14th, 2002, 04:46 PM

Quote:

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.

nickb · Mar 14th, 2002, 04:54 PM

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

NateO · Mar 14th, 2002, 04:57 PM

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 ]

Aladin Akyurek · Mar 14th, 2002, 05:00 PM

Quote:

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.

anno · Mar 14th, 2002, 05:36 PM

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 ]

NateO · Mar 14th, 2002, 05:40 PM

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 ]

2077delta · Mar 14th, 2002, 06:24 PM

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 ]

nickb · Mar 15th, 2002, 09:43 AM

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..

NateO · Mar 15th, 2002, 05:03 PM

Nick, I like your attitude!

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

Happy St. Patty's!

Mar 15th, 2002, 05:03 PM	#10
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Nick, I like your attitude! Glad to be of assistance, all's well that ends well. Happy St. Patty's! __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 04:44 PM	#1
nickb New Member Join Date: Mar 2002 Posts: 3	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 ]

Mar 14th, 2002, 04:54 PM	#3
nickb New Member Join Date: Mar 2002 Posts: 3	Sorry...I just copied that from a email to a friend ...can we attach excel files?

Mar 14th, 2002, 04:57 PM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

Mar 14th, 2002, 05:36 PM	#6
anno Board Regular Join Date: Feb 2002 Posts: 202	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 ]

Mar 14th, 2002, 05:40 PM	#7
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

Mar 14th, 2002, 06:24 PM	#8
2077delta Board Regular Join Date: Feb 2002 Posts: 217	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 ]

Mar 15th, 2002, 09:43 AM	#9
nickb New Member Join Date: Mar 2002 Posts: 3	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..