| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif question
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2")))
-- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
Then you would be best to put a calculation formula in the same cell in
every sheet, and then sum those cells. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
slight change in Bob's formula
=SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56*pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
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: Then you would be best to put a calculation formula in the same cell in every sheet, and then sum those cells. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
What are the sheet names?
-- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
Sheet's names are actually dates (05.01.2009 ; 06.01.2009...etc. without
sunday) Thanks. "Bob Phillips" a scris: What are the sheet names? -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
i hope you can help me with this code. i really need it.
Thanks allot. "Bob Phillips" a scris: What are the sheet names? -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
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"))) On Feb 12, 3:55*pm, puiuluipui wrote: 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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif question
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 -- __________________________________ HTH Bob "puiuluipui" wrote in message ... i hope you can help me with this code. i really need it. Thanks allot. "Bob Phillips" a scris: What are the sheet names? -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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: slight change in Bob's formula =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("1 :31")) &"!"&"A1:A10"),"john",INDIRECT("sheet"&ROW(INDIREC T("1:31")) &"!"&"B1:B10"))) On Feb 12, 1:56 pm, puiuluipui wrote: 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: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:31"))&"'!A1"),"John",INDIRECT("'Sheet"&ROW(INDIR ECT("1:31"))&"'!B2"))) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF question | Excel Worksheet Functions | |||
| Another SUMIF question | Excel Worksheet Functions | |||
| SUMIF Question | Excel Worksheet Functions | |||
| SUMIF question | Excel Discussion (Misc queries) | |||
| SUMIF question (I think) | Excel Worksheet Functions | |||
Linear Mode
