Sum

[puiuluipui]

Hi, i need the sum of all the times but calculated by a person name.
Ex:

sheet 2
A B C
nr name time
1 john 00:02:00
2 mary 00:01:00
3 john 00:02:30
4 cris 00:05:00
5 john 00:01:00
6 mary 00:01:00
7 cris 00:05:00


And i need in sheet 1, the sum of each person time, like this:

sheet 1
A B C
nr name time
1 john 00:05:30
2 mary 00:02:00
3 cris 00:10:00

Can this be done?
Thanks!

 

[Marcelo]

=SUMIF(B2:B8,B10,C2:C8)

b10 = John

or

=sumif(b2:b8,"John",c2:c8)

format as hh:mm:ss
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"puiuluipui" escreveu:

 

[puiuluipui]

Hi Marcelo, your code it's working, but there is a small problem. In the
time column i have something like this:

Late 00:01:23

So, the code needs to sum "Late 00:01:23"+"Late 00:05:00"...etc.
I thought the code will sum this anyway, but it's working only if i remove
"Late".
Is there any way to sum text and numbers together? (with "Late"?)

Thanks!


"Marcelo" a scris:

 

[Marcelo]

if you have the word "late" with the time you have a text on the cell, and
not a number, so it will not work you hould remove the late do sum it. one
way is use an auxilar column to separte the late and the time and you will
maintaim the information.

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"puiuluipui" escreveu:

 

[puiuluipui]

I have this formula that sum text with numbers (Late 00:00:02), but i don't
know how to combine this two formulas. Is there any way to make this two
formulas to work together?

=SUMPRODUCT(--SUBSTITUTE(B6:Y6,"Late ",""))

=SUMIF(B2:B8,B10,C2:C8) -your formula-

Thanks!

"Marcelo" a scris:

 

[David Biddulph]

=SUMIF(B2:B8,B10,--SUBSTITUTE(C2:C8,"Late","")) ?

 

[Marcelo]

Try it

=SUMPRODUCT(($B$1:$B$6=B10)*SUBSTITUTE($C$1:$C$6,"late ",""))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"puiuluipui" escreveu:

 

[puiuluipui]

It's working great. I have only one more problem. If i have an empty row, the
code display "#VALUE!". Can you make the code ignore empty rows and sum
everything in column?
Thanks!

"Marcelo" a scris: