Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)

[Purple_Girl]

Hello,

Would anyone be able to help me figure out a formula that I am stumped on? Basically, I've got a table of data that has Names, Total # Errors (for each name), and Categories (this is the "type" of error for each error listed).

My job is to find the Quality % for each person and for each Category. Which means, for each name, I have 1.) find out how many (the "count" - not sum) of Total Errors they have, and 2.) find out how many of those errors were made in which Category. From there, I can do a simple formula to calculate their Quality % for each Category. The challenging part, for me, is finding out what formula I need to use to find the 1. and 2. listed above.

Can anyone help to figure out a formula for me to use? Here's a sample of the table that I'm working on...

http://tinypic.com/r/j5jfv5/7

Please let me know if you can help!
Thanks SO much!!
Gina

 

[Cease]

Purple_Girl,

Glad it works so far :D

to match it to a category, the most straightforward way would be to add a criteria range and criteria to the countifs function.

If names are in column a, ttl errors are in b, and category is c: throw this in d2:

=countifs(A:A,a2,B:B,">0",C:C,c2)
or instead of "c2":
=countifs(A:A,a2,B:B,">0",C:C,"Category Name")

Hope it helps :D

 

[Purple_Girl]

THANK YOU SO MUCH!!! You are a HUGE help!!! This works GREAT for my project!!! Many thanks to you!!!

 

[Cease]

Anytime,

As much great info as I've gotten here, I'm glad I can return the favor for a change.

Cheers

 

[Purple_Girl]

@ Cease (or anyone out there): Oh - another question!! I've now come across the fact that I am going to need to add a condition to one of the references in my formula:

=IF($W15=0," ",COUNTIFS($G:$G,$W15,$C:$C,">0",$I:$I,$AB$8,$A:$A,$AM$5))

W15 is the Name that I want to find
Col G is the list of names
Col C is the list of TTL Errors values
Col I is the list of Categories
AB8 is the Category name that I want to find
Col A is the list of months
AM5 is the month that you select from the drop down list...so, all months are listed and the formula works great when a month is chosen, BUT when "All" is chosen in the drop down, the formula does not know what to do.

My question is, how do I write the formula to include the condition that if "All" is selected, it must still calculate the values??

Thanks to all who respond!!!!

 

[Purple_Girl]

All, I've figured it out! Here's my new formula:

=IF($W14=0," ",IF($AM$5="(ALL)",COUNTIFS($G:$G,$W15,$C:$C,">0",$I:$I,$AB$8),COUNTIFS($G:$G,$W15,$C:$C,">0",$I:$I,$AB$8,$A:$A,$AM$5)))