need a formula....help!!!

nickb

New Member
Joined
Mar 13, 2002
Messages
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-14 16:01
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-14 16:41
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
Nick, I like your attitude! :)

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

Happy St. Patty's!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top