Matching and calculating 2 worksheets

[mayanair]

Hi,
I need some help with Excel Functions. I know this does not need much
programming but to just have the right functions to use. I have the
idea just dont know how to compute it. Kindly help

Right so the situation goes like this.

I have one worksheet that has names and IDs. the second worksheet
contains the IDs which is repeated and another column that shows which
ID has a completed case which is stated as COMPLETED.In short an
example as below:

1st Worksheet
ID Name
abc123 John
def345 Angela


2nd Worksheet

ID Status
abc123 Completed
def345 Pending
abc123 Pending
def345 Completed
abc123 Completed

thus the first work sheet will populate as
ID Name Number of completed case
abc123 2
def123 1

How do i compute this?

Appreciate the help

 

[Superdude]

One way is to use array functions - assuming that this data is in the
top left of your worksheets then try

=SUM(IF('2nd Worksheet'!$A$1:$A$5=A1,1,0)*IF('2nd
Worksheet'!$B$1:$B$5="Completed",1,0))

Then remember to press Ctrl-Shift-Enter and you're done.

 

[mayanair]

thanks,

im still trying to apply it to my prog

 

[mayanair]

hi,

how do you make a formula like below to be dynamic?

=SUMPRODUCT(--(Sheet2!A$1:A$100=A1),--(Sheet2!B$1:B$100="Yes"))

Coz if were to increase the amount of data in the sheet then ill hav
to manually change the max cell limit.

Pls enlighten me on this

Thanks!! :