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)

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

thanks in advance

ravi kumar

Assuming Table 1 is in A1:F20, and table 2 is in A23:C28

=SUMPRODUCT(--($F$1:$F$20=$A24),--($D$1:$D$20=B$23))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ravi" wrote in message
...
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)

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

thanks in advance

ravi kumar

Hi Bob .........i have get the count but instead of 1 i goe 2 in "Yet to
Start" because 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 considered as 1 find the example below

The result should be Chennai - 3 and Delhi - 4 but if i use the below
formula i get Chennai - 5 and Delhi - 5

i got total count is 10 but it should be 7 only

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

"Bob Phillips" wrote:

Assuming Table 1 is in A1:F20, and table 2 is in A23:C28

=SUMPRODUCT(--($F$1:$F$20=$A24),--($D$1:$D$20=B$23))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ravi" wrote in message
...
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)

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

thanks in advance

ravi kumar

Your problem is occuring because of the multiple lines for single projects.
In order to get away from this 'duplicate counting', I added column A (your
SL#), to the SUMPRODUCT formula, so that it looks like this:

=SUMPRODUCT(--($E$2:$E$11=$A22),--($C$2:$C$11=B$21),--($A$2:$A$11<""))

This assumes your main table of data is in cells A2:E11, and your 4
different status types are in cells A22:A25, and your two locations are in
cells B21:C21. Type the formula above in cell B22, then copy to cell C22,
then copy B22:C22 down to B25:C25.

NOTE: This will only tally the project status if there is an SL# on that
row, so in your data set as given, there is no tally for Pending in Delhi on
project 105. Not sure if the 2 different locations for the same project is a
typo.

--
John C


"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)

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

thanks in advance

ravi kumar