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?

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

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" <(E-Mail Removed)> wrote in message
news:39387B86-2A90-4656-913D-(E-Mail Removed)...
> 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
>

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 <Stu...@discussions.microsoft.com> wrote:
> 2009 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * *
> Title * * * *Grade * * * *QTR 1 *QTR 2 *QTR 3 * QTR4
> 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 thenfor
> that grade add up all the total manhours ignoring the rest of the manhours