Sumproduct how can i use

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

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

 

[Bob Phillips]

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]

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

 

[John C]

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.