Can I select cells 3 columns away from a value in a line

[pencil32]

In column A I have several Names, In column D I have certain values.
In a cell I want to collect the values of the line in column D if a
certain name is on the same line in column A
I know if I use sum I will get the total value, but is there a way
automaticly to put the value there if the "name is in column A?



col A col B col C col D
col E col F

Name Start End Hours

Jack 9:00pm 3:00pm 6:00
John 10:00am 2:00pm 4:00
James 11:00am 5:00pm 6:00



Jack 9:00pm 3:00pm 6:00
John 10:00am 2:00pm 4:00
James 9:00am 1:00pm 4:00


Name
Tot hours
Jack
12:00 <<<<<< Formula for this cell to add the totals for Col D if the
name is in Col A.
John
8:00
James
10:00



I hope you understand what I need
Regards
Excel quest

 

[Pete_UK]

Use the SUMIF( ) function. Assume your data extends to row 20 and
starts in row 2, and that "Jack" is in A24, then the formula in B24
would be:

=SUMIF(A$2:A$20,A24,D$2$20)

The formula can be copied to B25:B26 to give totals for John and James.
You should format these cells as [h]:mm to display more than 24 hours.

Hope this helps.

Pete

 

[pencil32]

Thanks Pete
I was almost there when you replied, now I have it!
Using the Name as critera rather than a cell number

=SUMIF(A2:A100,"Jack",D2100)
or
SUMIF(A$2:A$100,"Jack",A$2:A$100)

Now heres another ,can I make the critera of this sum be automaticly
put the value of a previous cell

in other words, if the word "Jack" was in G40 and in the next cell
(h40) i wish to put this function SUMIF(A$2:A$100,"Jack",A$2:A$100)
could I use another "critera" to express the value of the previous
cell, rather than having to type "jack" or a cell number

SUMIF(A$2:A$100," previous cell value ",A$2:A$100)



Excel perfect

 

[Pete_UK]

I don't see what is wrong with:

=SUMIF(A$2:A$100,G40,D$2$100)

typed into cell H40 - why do you want to avoid cell references?

With this formula you can have other names in G41, G42 etc, and all you
have to do is copy the formula down to get the totals for each person -
the formula will automatically adjust to these cell references.

Hope this helps.

Pete