Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
Sumif? | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |