How to get the Total count

[Ravi]

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

 

[M Kan]

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.

 

[Stefi]

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:

 

[Ravi]

thanks a ton stefi....................

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:

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Ravi†ezt írta:

 

[Ravi]

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]

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: