| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in multi sheet
Greeting,
I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 .. .. .. .. So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
try this
in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37*am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID * * * * * * Name * * * * * * * * * *No. of Absent 1 * * * * * * * * * * * John * * * * * * * * * * * * * *1 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 2 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 1 Sheet (Feb) Employee ID * * * * * * Name * * * * * * * * * *No. of Absent 1 * * * * * * * * * * * John * * * * * * * * * * * * * *3 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 1 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID * * * * * * Name * * * * * * * * * *Absent Total 1 * * * * * * * * * * * John * * * * * * * * * * * * * *4 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 3 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 5 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
Hi Muddan,
It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April
in summay sheet - Cell G2:G5 mention the sheet names. (eg., Jan,Feb,Mar....) in all sheets data are in Col A, Col B, Col C I hope. On Nov 17, 1:05*pm, ghost wrote: Hi Muddan, It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID * * * * * * Name * * * * * * * * * *No. of Absent 1 * * * * * * * * * * * John * * * * * * * * * * * * * *1 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 2 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 1 Sheet (Feb) Employee ID * * * * * * Name * * * * * * * * * *No. of Absent 1 * * * * * * * * * * * John * * * * * * * * * * * * * *3 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 1 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID * * * * * * Name * * * * * * * * * *Absent Total 1 * * * * * * * * * * * John * * * * * * * * * * * * * *4 2 * * * * * * * * * * * aaa * * * * * * * * * * * * * * 3 3 * * * * * * * * * * * bbb * * * * * * * * * * * * * * 5- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
hi Muddan,
thank you , but would you please explain more for the other parts of the formula "muddan madhu" wrote: suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April in summay sheet - Cell G2:G5 mention the sheet names. (eg., Jan,Feb,Mar....) in all sheets data are in Col A, Col B, Col C I hope. On Nov 17, 1:05 pm, ghost wrote: Hi Muddan, It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
Try this alternative, no need for the values in G2:...
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(YEAR(TODA Y()),{1;2;3;4;5;6;7;8;9;10;11;12},1),"mmm")&"'!B2: B4"),B2, INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),{1;2;3;4;5;6; 7;8;9;10;11;12},1),"mmm")&"'!C2:C4"))) -- __________________________________ HTH Bob "ghost" wrote in message ... hi Muddan, thank you , but would you please explain more for the other parts of the formula "muddan madhu" wrote: suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April in summay sheet - Cell G2:G5 mention the sheet names. (eg., Jan,Feb,Mar....) in all sheets data are in Col A, Col B, Col C I hope. On Nov 17, 1:05 pm, ghost wrote: Hi Muddan, It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
Hi, Bob
Thanks but the data is not "DATE". help is still needed. "Bob Phillips" wrote: Try this alternative, no need for the values in G2:... =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(YEAR(TODA Y()),{1;2;3;4;5;6;7;8;9;10;11;12},1),"mmm")&"'!B2: B4"),B2, INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),{1;2;3;4;5;6; 7;8;9;10;11;12},1),"mmm")&"'!C2:C4"))) -- __________________________________ HTH Bob "ghost" wrote in message ... hi Muddan, thank you , but would you please explain more for the other parts of the formula "muddan madhu" wrote: suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April in summay sheet - Cell G2:G5 mention the sheet names. (eg., Jan,Feb,Mar....) in all sheets data are in Col A, Col B, Col C I hope. On Nov 17, 1:05 pm, ghost wrote: Hi Muddan, It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
countif in multi sheet
You'll need to explain that remark, it passed about 5,000 ft above me.
-- __________________________________ HTH Bob "Jon" wrote in message ... Hi, Bob Thanks but the data is not "DATE". help is still needed. "Bob Phillips" wrote: Try this alternative, no need for the values in G2:... =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(YEAR(TODA Y()),{1;2;3;4;5;6;7;8;9;10;11;12},1),"mmm")&"'!B2: B4"),B2, INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),{1;2;3;4;5;6; 7;8;9;10;11;12},1),"mmm")&"'!C2:C4"))) -- __________________________________ HTH Bob "ghost" wrote in message ... hi Muddan, thank you , but would you please explain more for the other parts of the formula "muddan madhu" wrote: suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April in summay sheet - Cell G2:G5 mention the sheet names. (eg., Jan,Feb,Mar....) in all sheets data are in Col A, Col B, Col C I hope. On Nov 17, 1:05 pm, ghost wrote: Hi Muddan, It does not work and I do not know how it works, there is no indecators for sheets!!!! "muddan madhu" wrote: try this in summary sheet from G2:G4 mentioned sheet name as Jan,Feb,Mar,April In summary sheet cell C2 put this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$5&"'!B2:B4" ),B2,INDIRECT("'"&$G $2:$G$5&"'!C2:C4"))) On Nov 17, 10:37 am, ghost wrote: Greeting, I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial). Sheet (Jan) Employee ID Name No. of Absent 1 John 1 2 aaa 2 3 bbb 1 Sheet (Feb) Employee ID Name No. of Absent 1 John 3 2 aaa 1 3 bbb 4 . . . . So on. What I want to do is how to summarize the above table as follows by using sum if or count if: Sheet (summary) Employee ID Name Absent Total 1 John 4 2 aaa 3 3 bbb 5- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with multi sheet workbook | Excel Worksheet Functions | |||
| Multi-sheet sumif help | Excel Worksheet Functions | |||
| multi-sheet sort | Excel Discussion (Misc queries) | |||
| Multi-conditions with SUMPRODUCT and COUNTIF | Excel Worksheet Functions | |||
Linear Mode
