Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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=536261 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching and calculating 2 worksheets
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. -- Superdude ------------------------------------------------------------------------ Superdude's Profile: http://www.excelforum.com/member.php...o&userid=33850 View this thread: http://www.excelforum.com/showthread...hreadid=536261 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching and calculating 2 worksheets
thanks, im still trying to apply it to my prog -- mayanair ------------------------------------------------------------------------ mayanair's Profile: http://www.excelforum.com/member.php...o&userid=33847 View this thread: http://www.excelforum.com/showthread...hreadid=536261 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching and calculating 2 worksheets
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!! : -- mayanai ----------------------------------------------------------------------- mayanair's Profile: http://www.excelforum.com/member.php...fo&userid=3384 View this thread: http://www.excelforum.com/showthread.php?threadid=53626 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching 2 worksheets | Excel Worksheet Functions | |||
matching 2 worksheets together | Excel Discussion (Misc queries) | |||
Matching and calculating 2 worksheets | Excel Worksheet Functions | |||
MATCHing through multiple worksheets | Excel Worksheet Functions | |||
CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS! | Excel Programming |