Conditional lookups based on other cells

[Dzander]

Hi guys,
I'm completely stumped by getting this formula right, Basically i have a rejects sheet with preventable and unpreventable rejects in it.
I need to have the number of rejects on the condition they are preventable. The data has column A with the sales persons name and in column H "was it preventable" I would like to have a table that summarises the list to be:

Sales person | # of preventable errors
Joe Bloggs
peter Smit

The names would be populated in column A, but i need to know the number of jobs that have been rejected per person. for example for Joe Bloggs there might be 10 jobs rejected but only 5 of them are listed as preventable, so the result would be 5

Any help with this would be great as it's driving me crazy!!

 

[cmhoz]

If you just want a list that shows the people who had preventable errors, and how many. I would use a pivot table.

If you want a list of all people, and those with 0 errors to show 0, you could add a concatenated column to the end of your data that concatenates the user name and the preventable detail from column 'H'. Then, make a list of all the user names and to get the number of preventable errors, do a 'countif' on your new concatenation column eg countif(mynewcolumn, username&"yes") - assuming column H says 'Yes' for those that were preventable.

 

[Aladin Akyurek]

Try to post 5 rows of data where we need to look at for setting up the summary.

 

[Dzander]

If you want a list of all people, and those with 0 errors to show 0, you could add a concatenated column to the end of your data that concatenates the user name and the preventable detail from column 'H'. Then, make a list of all the user names and to get the number of preventable errors, do a 'countif' on your new concatenation column eg countif(mynewcolumn, username&"yes") - assuming column H says 'Yes' for those that were preventable.

This is great thanks heaps, I think it's a great way of doing it, but i love it!!! I'm trying not to use too many pivot tables as they get far to chunky and annoying without adding much value! so this is a great solution!