help in excel functions

[ramis344]

i am learning excel now and i have this issue that i could not solve i
. i have 2 columns :the first includes the names of some projects .th
second includes the number of hours of each project.each project ro
takes more than one row in the column hours.so i need to know how i ge
the whole hours of a specific project "in details please" ...thank

 

[Katherine Coombs]

Hi Ramis344,

It is always helpful if you can paste some of your worksheet into your
message so that we can see the format of your data etc. I'm guessing that
it looks like this:

Project Name Hours
katherine 35
benjamin 5
katherine 20
billy gates 15


and what you want is:

Project Name Hours
katherine 55
benjamin 5
billy gates 15


The easiest way to do this is via a Pivot Table. Select your data range and
go to Data-->Pivot Table and Pivot Chart Report. Select Pivot Table and
click Next. Confirm that the data range that Excel thinks that you want for
the Pivot Table is correct (you can see a dashed line around the range in
your spreadsheet. If this is correct, click on Next. You can then choose
if you want the Pivot Table to be in a separate worksheet, or in the
existing worksheet. It doesn't make any difference, it's just the location
of where it will be put when it's created although if it's going to be very
large it might be easier on its own worksheet. Click on Finish.

Click on Project Name and drag it to where it says "Drop Row Fields Here".
Click on Hours and drag it to where it says "Drop Data Items Here".

Hey presto, there is your data table.

HTH,
Katherine

 

[Arvi Laanemets]

Hi

Another way is to create a summary sheet.

Your original table must have the structure like Katherine assumed, i.e. you
have to repeat the project name/code every time you enter hours for project.
The order the rows are in table is free -the data mustn't be sorted. Let's
assume the original table is on sheet Sheet1 in columns A:B with headers in
first row

You create an additional sheet, p.e. Summary, create on this sheet a table
with sructure like Project, SumHours, and enter all projects into this sheet
once.
Into B2 enter the formula
=IF(A2="","",SUMIF(Sheet1!$A:$A,A2,Sheet1!$B:$B))
and copy it down (you can have the formula copied further down as you have
projects registered - for future)

An adiitional bonus:
You can define a named range Projects (Insert.Name.Define from menu, enter
range name and then into 'Refers to' field the formula
=OFFSET(Summary!$A$2,,,COUNTIF(Summary!$A:$A,"<>")-1,1)

Now you can format the Project column on Sheet1 using Data.Validation.List
with Source=Projects, to get drop-downs with project names on Sheet1