match name and show total result

[Steve]

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.

 

[Paul C]

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

 

[Francis]

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