software

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 Angela2nd 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--
mayanair
------------------------------------------------------------------------
mayanair's Profile: www.excelforum.com/member.php...oamp;userid=33847
View this thread: www.excelforum.com/showthread...hreadid=536267Hi!

Assume this is the layout of sheet1:

..................A.................B............. .........C
1..............ID.............Name............Comp leted
2..........abc123..........John................for mula
3..........def345..........Angela.............form ula

Enter this formula in C2:

=SUMPRODUCT(--(Sheet2!A$2:A$6=A2),--(Sheet2!B$2:B$6=C$1))

Copy down as needed.

Biff

quot;mayanairquot; gt; wrote in
message ...
gt;
gt; Hi,
gt; I need some help with Excel Functions. I know this does not need much
gt; programming but to just have the right functions to use. I have the
gt; idea just dont know how to compute it. Kindly help
gt;
gt; Right so the situation goes like this.
gt;
gt; I have one worksheet that has names and IDs. the second worksheet
gt; contains the IDs which is repeated and another column that shows which
gt; ID has a completed case which is stated as COMPLETED.In short an
gt; example as below:
gt;
gt; 1st Worksheet
gt; ID Name
gt; abc123 John
gt; def345 Angela
gt;
gt;
gt; 2nd Worksheet
gt;
gt; ID Status
gt; abc123 Completed
gt; def345 Pending
gt; abc123 Pending
gt; def345 Completed
gt; abc123 Completed
gt;
gt; thus the first work sheet will populate as
gt; ID Name Number of completed case
gt; abc123 2
gt; def123 1
gt;
gt; How do i compute this?
gt;
gt; Appreciate the help
gt;
gt;
gt; --
gt; mayanair
gt; ------------------------------------------------------------------------
gt; mayanair's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33847
gt; View this thread: www.excelforum.com/showthread...hreadid=536267
gt;

Hi.

Thanks for the prompt reply.

I tried the formula, but it doesnt seem to work, resulted in some
circular reference error.

maybe ill just brief again on how the 2 sheets look like

Sheet 1
A.....................B........................C
Name..............ID........................Number of Completed Cases
John...............abc......................
Angela.............def.....................
Nick.................ghi.....................

Sheet 2

A......................B
ID.....................Completed
abc...................Yes
def....................No
abc...................No
ghi....................Yes
def....................Yes
abc....................No
ghi.....................No
def.....................Yes

The formula will match the ID from sheet 1 and sheet 2 and count only
the number of ID that has a Yes. The end result in sheet one will look
like below:

A.....................B........................C
Name..............ID........................Number of Completed Cases
John...............abc......................1
Angela.............def.....................2
Nick.................ghi.....................1

Hope this helps.

Thanks again --
mayanair
------------------------------------------------------------------------
mayanair's Profile: www.excelforum.com/member.php...oamp;userid=33847
View this thread: www.excelforum.com/showthread...hreadid=536267That doesn't look anything like your first post! lt;ggt;

Try this:

=SUMPRODUCT(--(Sheet2!A$2:A$9=B2),--(Sheet2!B$2:B$9=quot;Yesquot;))

Biff

quot;mayanairquot; gt; wrote in
message ...
gt;
gt; Hi.
gt;
gt; Thanks for the prompt reply.
gt;
gt; I tried the formula, but it doesnt seem to work, resulted in some
gt; circular reference error.
gt;
gt; maybe ill just brief again on how the 2 sheets look like
gt;
gt; Sheet 1
gt; A.....................B........................C
gt; Name..............ID........................Number of Completed Cases
gt; John...............abc......................
gt; Angela.............def.....................
gt; Nick.................ghi.....................
gt;
gt; Sheet 2
gt;
gt; A......................B
gt; ID.....................Completed
gt; abc...................Yes
gt; def....................No
gt; abc...................No
gt; ghi....................Yes
gt; def....................Yes
gt; abc....................No
gt; ghi.....................No
gt; def.....................Yes
gt;
gt; The formula will match the ID from sheet 1 and sheet 2 and count only
gt; the number of ID that has a Yes. The end result in sheet one will look
gt; like below:
gt;
gt; A.....................B........................C
gt; Name..............ID........................Number of Completed Cases
gt; John...............abc......................1
gt; Angela.............def.....................2
gt; Nick.................ghi.....................1
gt;
gt; Hope this helps.
gt;
gt; Thanks again
gt;
gt;
gt; --
gt; mayanair
gt; ------------------------------------------------------------------------
gt; mayanair's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33847
gt; View this thread: www.excelforum.com/showthread...hreadid=536267
gt;

Hi Biff,

Sorry for the vague desc in the first post

Thanks, it works now but i noticed that the formula is quite static,
does not support if the data increases unless i manually change it. How
to make dynamic?

Thanks.--
mayanair
------------------------------------------------------------------------
mayanair's Profile: www.excelforum.com/member.php...oamp;userid=33847
View this thread: www.excelforum.com/showthread...hreadid=536267You can either create a dynamic named range for the data on sheet2:

contextures.com/xlNames01.html#Dynamic

Or, just use a larger range size that allows for expansion:

=SUMPRODUCT(--(Sheet2!A$2:A$1000=B2),--(Sheet2!B$2:B$1000=quot;Yesquot;))

Biff

quot;mayanairquot; gt; wrote in
message ...
gt;
gt; Hi Biff,
gt;
gt; Sorry for the vague desc in the first post
gt;
gt; Thanks, it works now but i noticed that the formula is quite static,
gt; does not support if the data increases unless i manually change it. How
gt; to make dynamic?
gt;
gt; Thanks.
gt;
gt;
gt; --
gt; mayanair
gt; ------------------------------------------------------------------------
gt; mayanair's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33847
gt; View this thread: www.excelforum.com/showthread...hreadid=536267
gt;