|
How to get the Total count
Hi, I have two tables like the below ( i have hundreds of data below is the
example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
This should work
sumproduct(--(range_region=b$2),--(range_status=$A3)) Assuming that B$2 = your first city in Table 1, Chennai and $A3 is your first status in Table 1, Yet to start. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Ravi" wrote: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
In Table 1 Status being in Col A, Regions in B and C
=SUMPRODUCT(--(Table2!$G$2:$G$11=$A2),--(Table2!$C$2:$C$11=B$1)) Fill it to the right and down! Regards, Stefi €˛Ravi€¯ ezt Ć*rta: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
thanks a ton stefi....................
"Stefi" wrote: In Table 1 Status being in Col A, Regions in B and C =SUMPRODUCT(--(Table2!$G$2:$G$11=$A2),--(Table2!$C$2:$C$11=B$1)) Fill it to the right and down! Regards, Stefi €˛Ravi€¯ ezt Ć*rta: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
You are welcome! Thanks for the feedback!
Stefi €˛Ravi€¯ ezt Ć*rta: thanks a ton stefi.................... "Stefi" wrote: In Table 1 Status being in Col A, Regions in B and C =SUMPRODUCT(--(Table2!$G$2:$G$11=$A2),--(Table2!$C$2:$C$11=B$1)) Fill it to the right and down! Regards, Stefi €˛Ravi€¯ ezt Ć*rta: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
Hi Stefi.........one more clarification needed, i have two categories ( it
may be 3 or 4 also ) in TABLE 2 if i count the current status in TABLE 2 to get the count in TABLE 1 if i have two categories it should be consided as 1 find the example below please help............ Eg : Actual count should like this Yet to Start 1 Delivered 3 Pending 1 In Process 2 Total 7 After putting formula it like this Yet to Start 2 Delivered 4 Pending 2 In Process 2 Total 10 TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Category Region QC Current Status 1 101 C06 Chennai Ravi Yet to Start 101 INP Chennai Ravi Yet to Start 2 103 C06 Chennai Ravi Delivered 103 INP Chennai Ravi Delivered 3 105 C07 Chennai Ravi Pending 105 INS Delhi Ravi Pending 4 107 C08 Delhi Ravi Delivered 5 108 C09 Delhi Ravi Delivered 6 109 C08 Delhi Ravi In Process 7 110 C08 Delhi Ravi In Process "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €˛Ravi€¯ ezt Ć*rta: thanks a ton stefi.................... "Stefi" wrote: In Table 1 Status being in Col A, Regions in B and C =SUMPRODUCT(--(Table2!$G$2:$G$11=$A2),--(Table2!$C$2:$C$11=B$1)) Fill it to the right and down! Regards, Stefi €˛Ravi€¯ ezt Ć*rta: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
How to get the Total count
Sorry, Ravi, I couldn't understand the problem! What do you call category? In
your Table 2 there are several categories (C06, INP, C07, etc.). I couldn't figure out how did you calculate this table of you: Eg : Actual count should like this Yet to Start 1 Delivered 3 Pending 1 In Process 2 Total 7 Please, clarify! Stefi €˛Ravi€¯ ezt Ć*rta: Hi Stefi.........one more clarification needed, i have two categories ( it may be 3 or 4 also ) in TABLE 2 if i count the current status in TABLE 2 to get the count in TABLE 1 if i have two categories it should be consided as 1 find the example below please help............ Eg : Actual count should like this Yet to Start 1 Delivered 3 Pending 1 In Process 2 Total 7 After putting formula it like this Yet to Start 2 Delivered 4 Pending 2 In Process 2 Total 10 TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Category Region QC Current Status 1 101 C06 Chennai Ravi Yet to Start 101 INP Chennai Ravi Yet to Start 2 103 C06 Chennai Ravi Delivered 103 INP Chennai Ravi Delivered 3 105 C07 Chennai Ravi Pending 105 INS Delhi Ravi Pending 4 107 C08 Delhi Ravi Delivered 5 108 C09 Delhi Ravi Delivered 6 109 C08 Delhi Ravi In Process 7 110 C08 Delhi Ravi In Process "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €˛Ravi€¯ ezt Ć*rta: thanks a ton stefi.................... "Stefi" wrote: In Table 1 Status being in Col A, Regions in B and C =SUMPRODUCT(--(Table2!$G$2:$G$11=$A2),--(Table2!$C$2:$C$11=B$1)) Fill it to the right and down! Regards, Stefi €˛Ravi€¯ ezt Ć*rta: Hi, I have two tables like the below ( i have hundreds of data below is the example) and i want to count the current status count in chennai or Delhi coloumn (whereever zero) if i change the status in current status coloumn in TABLE 2 then the total count will automatically calculate in TABLE 1 chennai or Delhi fileld (i tried Countif function but i am not able to get the correct count)..please help TABLE : 1 Status Chennai Delhi Yet to Start 0 0 In Process 0 0 Pending - External 0 0 Delivered 0 0 Total 0 0 TABLE : 2 Sl # Project # Region Start Date End Date QC Current Status 1 101 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 2 102 Chennai 07/09/2008 07/09/2008 Ravi Yet to Start 3 103 Chennai 07/10/2008 07/10/2008 Ravi Delivered 4 104 Chennai 07/10/2008 07/10/2008 Ravi Delivered 5 105 Chennai 07/10/2008 07/10/2008 Ravi Pending - External 6 106 Delhi 07/10/2008 07/10/2008 Ravi Pending - External 7 107 Delhi 07/09/2008 07/09/2008 Ravi Delivered 8 108 Delhi 07/11/2008 07/11/2008 Ravi Delivered 9 109 Delhi 07/11/2008 07/11/2008 Ravi In Process 10 110 Delhi 07/11/2008 07/11/2008 Ravi In Process Thanks regards Ravi Kumar |
| All times are GMT +1. The time now is 09:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com