Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
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 -- mayanair ------------------------------------------------------------------------ mayanair's Profile: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
Hi!
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 "mayanair" wrote in message ... 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 -- mayanair ------------------------------------------------------------------------ mayanair's Profile: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
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: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
That doesn't look anything like your first post! <g
Try this: =SUMPRODUCT(--(Sheet2!A$2:A$9=B2),--(Sheet2!B$2:B$9="Yes")) Biff "mayanair" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
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: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching and calculating 2 worksheets
You can either create a dynamic named range for the data on sheet2:
http://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="Yes")) Biff "mayanair" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536267 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |