Vlookup or IF formula to return count if name is populated

[Miguelluis]

Hi
I have one spredsheet where the date, name, team, results are recorded.

Date</SPAN>	Exec Name</SPAN>	Team</SPAN>	Internal Process</SPAN>	Client Impact?</SPAN>	Final Status</SPAN>
27/01/2013</SPAN>	Miguel Luis</SPAN>	Tools team</SPAN>	Fail	Yes	Fail
28/01/2013</SPAN>	Miguel Luis</SPAN>	Tools team</SPAN>	Pass	No	Pass
29/01/2013</SPAN>	Mr Excel</SPAN>	Tools team</SPAN>	Pass	no	Pass

<TBODY>
</TBODY>

What I am trying to do is when a name is displayed here one or more times, it will then show this on a diferent sheet and count how many fails and passes each person has had.
like so:

Name</SPAN>
		Pass</SPAN>	Fail</SPAN>	Total QC'd</SPAN>	%</SPAN>
Miguel Luis</SPAN>		1</SPAN>	1</SPAN>	2</SPAN>	50%</SPAN>
Mr Excel</SPAN>		1</SPAN>	0</SPAN>	1</SPAN>	100%</SPAN>

<TBODY>
</TBODY>

Can you help?

Thank you
Miguel Luis

 

[pplstuff]

Have you tried to use =sumifs()?

 

[MrVillareal]

Hi
I have one spredsheet where the date, name, team, results are recorded.

Date	Exec Name	Team	Internal Process	Client Impact?	Final Status
27/01/2013	Miguel Luis	Tools team	Fail	Yes	Fail
28/01/2013	Miguel Luis	Tools team	Pass	No	Pass
29/01/2013	Mr Excel	Tools team	Pass	no	Pass

<tbody>
</tbody>

What I am trying to do is when a name is displayed here one or more times, it will then show this on a diferent sheet and count how many fails and passes each person has had.
like so:

Name
		Pass	Fail	Total QC'd	%
Miguel Luis		1	1	2	50%
Mr Excel		1	0	1	100%

<tbody>
</tbody>

Can you help?

Thank you
Miguel Luis

try:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Exec Name	Team	Internal Process	Client Impact?	Final Status			Pass	Fail	Total QC'd	%
2	27/01/2013	Miguel Luis	Tools team	Fail	Yes	Fail		Miguel Luis	1	1	2	50%
3	28/01/2013	Miguel Luis	Tools team	Pass	No	Pass		Mr Excel	1	0	1	100%
4	29/01/2013	Mr Excel	Tools team	Pass	no	Pass
xl5galry.xls

Cell Formulas
Range	Formula
I2	=COUNTIFS($B$2:$B$4,$H2,$F$2:$F$4,I$1)
I3	=COUNTIFS($B$2:$B$4,$H3,$F$2:$F$4,I$1)
J2	=COUNTIFS($B$2:$B$4,$H2,$F$2:$F$4,J$1)
J3	=COUNTIFS($B$2:$B$4,$H3,$F$2:$F$4,J$1)
K2	=SUM(I2:J2)
K3	=SUM(I3:J3)
L2	=I2/K2
L3	=I3/K3

 

[Miguelluis]

try:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Exec Name	Team	Internal Process	Client Impact?	Final Status			Pass	Fail	Total QC'd	%
2	27/01/2013	Miguel Luis	Tools team	Fail	Yes	Fail		Miguel Luis	1	1	2	50%
3	28/01/2013	Miguel Luis	Tools team	Pass	No	Pass		Mr Excel	1	0	1	100%
4	29/01/2013	Mr Excel	Tools team	Pass	no	Pass

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

xl5galry.xls​

Worksheet Formulas Cell Formula I2 =COUNTIFS($B$2:$B$4,$H2,$F$2:$F$4,I$1) J2 =COUNTIFS($B$2:$B$4,$H2,$F$2:$F$4,J$1) K2 =SUM(I2:J2) L2 =I2/K2 I3 =COUNTIFS($B$2:$B$4,$H3,$F$2:$F$4,I$1) J3 =COUNTIFS($B$2:$B$4,$H3,$F$2:$F$4,J$1) K3 =SUM(I3:J3) L3 =I3/K3 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Hello
Thank you for your repy. It worked but what if I wanted to colate the data in columns A to F in a separate sheet called Data and have the data from H to L displayed in a separate sheet called MTD.
Also, would this formula pull the name in column B automaticaly to column H or would I have to manualy type it?

Thank you

 

[Miguelluis]

Hello
Thank you for your repy. It worked but what if I wanted to colate the data in columns A to F in a separate sheet called Data and have the data from H to L displayed in a separate sheet called MTD.
Also, would this formula pull the name in column B automaticaly to column H or would I have to manualy type it?

Thank you