Sumif question

[puiuluipui]

Hi, i have

if A1=John

sheet 1 sheet 2 sheet 3 ....etc
A1=John A1=John A1=John
B2=00:01:29 B2=00:08:41 B2=00:24:04

I need the sum of all the cells corespondent to John
But John is not in every sheet, and i have 31 sheets.
Can this be done?

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIRECT("1:31"))&"'!B2")))

 

[puiuluipui]

Hi, i say it a little bit wrong. John is not every time in A1 and the late
time not allways in B2. my mistake. In sheet 2 could be in row 3 or 9..etc. I
just want the sum of the cell corespondent to John. If John is A3, late hours
are in B3. if John is in A9, the late hours are in B9. etc....if John is not
in some sheet, the code to skip that cell and count the cells sheets
containing John. Sorry again.
Can you help me with this onT?
thanks.

i need the code in
"Bob Phillips" a scris:

 

[Bob Phillips]

Then you would be best to put a calculation formula in the same cell in
every sheet, and then sum those cells.

 

[muddan madhu]

slight change in Bob's formula

=SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1:31"))
&"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIRECT("1:31"))
&"!"&"B1:B10")))

 

[puiuluipui]

it's a huge work. I have 1-15 names in every sheet and the names are random.
it would be easier, but i have diferent names in every sheet.
i need sheet 1 to calculate the sum of john's corespondant cell in every
sheet.

sheet 1
A B

1 john =all sheets (sheet 2.....sheet 31) john's corespondant
cell.

thanks.









"Bob Phillips" a scris:

 

[puiuluipui]

Hi, the code result is 00:00:00 .
It doesn' make a sum of the other hours.
All cells are formetted as time, except John cells.

Thanks.


"muddan madhu" a scris:

 

[Bob Phillips]

What are the sheet names?

 

[puiuluipui]

Sheet's names are actually dates (05.01.2009 ; 06.01.2009...etc. without
sunday)

Thanks.

"Bob Phillips" a scris:

 

[puiuluipui]

i hope you can help me with this code. i really need it.
Thanks allot.

"Bob Phillips" a scris:

 

[muddan madhu]

try this

one amendment - for sheet name starts with zero need to removed to
work with below formula
eg., 05.01.2009 change to 5.01.2009 , 06.01.2009 to 6.01.2009

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("5:31"))&(".01.2009")
&"!"&"A1:A10"),"john",INDIRECT(ROW(INDIRECT("5:31"))&(".01.2009")
&"!"&"B1:B10")))

 

[Bob Phillips]

I would put all the required sheet names in a rage, say M1:M3, and use

=SUMPRODUCT(SUMIF(INDIRECT("'"&M1:M3&"'!A2:A10"),"John",INDIRECT("'"&M1:M3&"'!B2:B10")))

just adjust that range to suit