Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating cells
I am using excel 2003, and trying to work out what is the easiet and best way
to run a a formula. the problem is i have a grading system in a cell which has 8 ranges. i also have beside this the total manhours calculated for that grade. the problem is the project is spread over various tasks and assignments, so multiple grades can be used all over the place. I am trying to see if there is a way to write a formula which would look up for just certain grades from the coloumn, and then from that look up the approiate manhours to give a year total. Is this possible or is it asking too much of the programme? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating cells
2009
Title Grade QTR 1 QTR 2 QTR 3 QTR 4 MAINTAINCE E2 5000 2500 1000 500 DEISGN PG1 2500 4000 3600 1500 ESTIMATING E2 1200 1200 1000 1200 This is a example of how the table looks. am trying to see if possible that a formula can look up the grade coloumn and select the approiate and then for that grade add up all the total manhours ignoring the rest of the manhours |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating cells
Hi Stuart,
With your sample data in A1 to F4, try this In B7 put E2 In B8 put PG1 Then put this formula in C7 =SUMPRODUCT(--($B$2:$B$4=$B7)*(C$2:C$4)) Now grab the fill handle and drag it across to F7 Release the fill handle, then grab it again and drag down to F8 Now put this in G7 =SUM(C7:F7) grab the fill handle and drag it down to G8 See how it works and apply the same principle to your full data. HTH Martin "Stuart" wrote in message ... 2009 Title Grade QTR 1 QTR 2 QTR 3 QTR 4 MAINTAINCE E2 5000 2500 1000 500 DEISGN PG1 2500 4000 3600 1500 ESTIMATING E2 1200 1200 1000 1200 This is a example of how the table looks. am trying to see if possible that a formula can look up the grade coloumn and select the approiate and then for that grade add up all the total manhours ignoring the rest of the manhours |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating cells
If you want to sum all the E2 hours for QTR1 column, then try this:
=SUMIF(B2:B20,"E2",C2:C20) assuming you have 20 rows of data. Copy across for the other quarters. Is this what you meant? Hope this helps. Pete On Jun 25, 9:46*am, Stuart wrote: 2009 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Title * * * *Grade * * * *QTR 1 *QTR 2 *QTR 3 * QTR 4 MAINTAINCE * *E2 * * * * 5000 * * * * 2500 * * * 1000 * * * * 500 DEISGN * * * * * PG1 * * * *2500 * * * *4000 * * * *3600 * * * * 1500 ESTIMATING * *E2 * * * * 1200 * * * * 1200 * * * *1000 * * * *1200 This is a example of how the table looks. am trying to see if possible that a formula can look up the grade coloumn and select the approiate and then for that grade add up all the total manhours ignoring the rest of the manhours |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating cells | Excel Discussion (Misc queries) | |||
Cells are not calculating on the fly | Excel Worksheet Functions | |||
calculating cells twice? | Excel Discussion (Misc queries) | |||
calculating cells | Excel Worksheet Functions | |||
Calculating cells | Excel Discussion (Misc queries) |