| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
match name and show total result
sorry if i didnt explain this too good earlier.
I have 2 worksheets. sheet 1 has names of people on it, sheet 2 has names of people and outcomes for work complete. How do i search the name from sheet one against sheet two and show how many comleted pieces of work has been carried out? eg. Sheet 1 ------------------ Anne Angela Andrea Andrew Sheet 2 --------------------------------- Anne complete Anne complete Anne not complete Angela complete Angela not complete Angela complete Angela complete Angela complete Angela not complete Andrea complete Andrea complete Andrea not complete Andrea complete Andrew complete Andrew complete Andrew complete Andrew not complete thank you in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
match name and show total result
On Sheet 1 you could use this formula in cell B1
=SUMPRODUCT(--(Sheet2!A1:A18=A1),--(Sheet2!B1:B18="Complete")) I am assuming that A1 would be the cell for Anne. If you have more data expand the ranges on Sheet 2 to match -- If this helps, please remember to click yes. "Steve" wrote: sorry if i didnt explain this too good earlier. I have 2 worksheets. sheet 1 has names of people on it, sheet 2 has names of people and outcomes for work complete. How do i search the name from sheet one against sheet two and show how many comleted pieces of work has been carried out? eg. Sheet 1 ------------------ Anne Angela Andrea Andrew Sheet 2 --------------------------------- Anne complete Anne complete Anne not complete Angela complete Angela not complete Angela complete Angela complete Angela complete Angela not complete Andrea complete Andrea complete Andrea not complete Andrea complete Andrew complete Andrew complete Andrew complete Andrew not complete thank you in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
match name and show total result
Hi Steve
try this, in Sheet1 in B1, type complete in C1, type not complete in B2, place =SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A2),--(Sheet1!$B$2:$B$20=Sheet2!B$1)) copy across and then down --- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Steve" wrote: sorry if i didnt explain this too good earlier. I have 2 worksheets. sheet 1 has names of people on it, sheet 2 has names of people and outcomes for work complete. How do i search the name from sheet one against sheet two and show how many comleted pieces of work has been carried out? eg. Sheet 1 ------------------ Anne Angela Andrea Andrew Sheet 2 --------------------------------- Anne complete Anne complete Anne not complete Angela complete Angela not complete Angela complete Angela complete Angela complete Angela not complete Andrea complete Andrea complete Andrea not complete Andrea complete Andrew complete Andrew complete Andrew complete Andrew not complete thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum command do not display the result eg:=sum(c5:c8)-total not | New Users to Excel | |||
| Formula result does not match displayed result | Excel Worksheet Functions | |||
| non match result | Excel Worksheet Functions | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| Match result is sometimes #N/A | Excel Discussion (Misc queries) | |||
Linear Mode
