sumif
 

Hi, i have in sheet 1 a table with names, and in other 30 or 31 sheets i have
some of this names.
Ex sheet 1

A B
Name Total
John 00:2:14
Sarah 00:1:29

All other sheets

A B

John late 00:12:44
Mary late 00:00:15
Sarah late 00:04:18

Some names can be in 10 sheets or more, maybe less.
I need the formula in B2 sheet 1 and the formula to search....if A1=John,
than the sum of John B2 in all sheets were can John be found is=....
B2 cell have "late" in it....can the formula calculate only the late hours?
late 00:02:00 + late 00:03:00 = late 00:05:00 ???



Thanks in advance.

sumif
 

if late and the number of hours are in different cells, shouldn't be
terrible. Just ridiculously long! :-)

=sumproduct(--(sheet2!A2:A500="John"),--(sheet2!b2:b500="late"),c2:c500)+sumproduct(--(sheet3!A2:A500="John"),--(sheet3!b2:b500="late"),c2:c500)

etc.

May not have enough room if that many sheets though!



"puiuluipui" wrote:

Hi, i have in sheet 1 a table with names, and in other 30 or 31 sheets i have
some of this names.
Ex sheet 1

A B
Name Total
John 00:2:14
Sarah 00:1:29

All other sheets

A B

John late 00:12:44
Mary late 00:00:15
Sarah late 00:04:18

Some names can be in 10 sheets or more, maybe less.
I need the formula in B2 sheet 1 and the formula to search....if A1=John,
than the sum of John B2 in all sheets were can John be found is=....
B2 cell have "late" in it....can the formula calculate only the late hours?
late 00:02:00 + late 00:03:00 = late 00:05:00 ???



Thanks in advance.

sumif
 

Hi, "late" is in the same cell with hours.
A B
"John" "late 00:01:54"

I need the code in "sheet 1, B2", and the code to make sum of all others
sheets "B2"

sheet 1
A B
1 John =(sheet 2, b2)+(sheet 3,B2)+(sheet 4,B2)....etc
2 Sarah =(sheet 2, b3)+(sheet 3,B3)+(sheet 4,B3)....etc

I guess that the code you gave me is not for this situation.
Can you help me with another code?

Thanks in advance.





"Sean Timmons" a scris:

if late and the number of hours are in different cells, shouldn't be
terrible. Just ridiculously long! :-)

=sumproduct(--(sheet2!A2:A500="John"),--(sheet2!b2:b500="late"),c2:c500)+sumproduct(--(sheet3!A2:A500="John"),--(sheet3!b2:b500="late"),c2:c500)

etc.

May not have enough room if that many sheets though!



"puiuluipui" wrote:

Hi, i have in sheet 1 a table with names, and in other 30 or 31 sheets i have
some of this names.
Ex sheet 1

A B
Name Total
John 00:2:14
Sarah 00:1:29

All other sheets

A B

John late 00:12:44
Mary late 00:00:15
Sarah late 00:04:18

Some names can be in 10 sheets or more, maybe less.
I need the formula in B2 sheet 1 and the formula to search....if A1=John,
than the sum of John B2 in all sheets were can John be found is=....
B2 cell have "late" in it....can the formula calculate only the late hours?
late 00:02:00 + late 00:03:00 = late 00:05:00 ???



Thanks in advance.