Excel to COUNTIF Joe Blogs makes a mistake.. Help

[jharcourt1]

Hi all,

I thought this was potentially an easy problem to solve. I am by no means an expert in excel but i can manage the straight forward formulas.

However i need someone's help in this scenario:

I have a set of people whom i am monitoring. My spreadsheet needs to capture how many mistake of which type each person makes.

Column A represents which type of error (4 possibilities (no error, admin error, minor error, major error))

Column B has a drop down list of all the people to monitor from.

I then have a seperate sheet (within the same excel document) where i will capture the details of how many of each mistake each person makes... which is where i am struggling to find a forumla.

Can any please help?

 

[barry houdini]

Which version of excel are you using? In Excel 2007 and later you can use COUNTIFS, e.g. with specific name in A2 and error in B1

=COUNTIFS(Sheet1!B:B,$A2,Sheet1!A:A,B$1)

 

[Trevor G]

Take a look at this sample, I have them all on the same sheet but you can adjust the formula accordingly. It is using CountIFs which is available in Excel 2007 or above

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Errors	Name			Names	No Error	Admin Error	Minor Error	Major Error
2	no error	Joe Bloggs			Joe Bloggs	1	2	1	1
3	admin error	Joe Bloggs			Ken Williams	1	0	1	0
4	admin error	Joe Bloggs			Anne Clarke	1	2	0	1
5	no error	Ken Williams
6	admin error	Anne Clarke
7	minor error	Joe Bloggs
8	admin error	Anne Clarke
9	major error	Anne Clarke
10	no error	Anne Clarke
11	minor error	Ken Williams
12	major error	Joe Bloggs
Sheet2

 

[jharcourt1]

Trevor G - that's perfect! thank you very much! A*

 

[Trevor G]

Pleased to have helped with a sample for you. Barries code was also spot on.