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

 

[powerdink]

I would use a pivot table to capture that, otherwise you could try using the COUNTIFS function if you don't understand pivots, but that would be more work.

 

[Cease]

Hey,
Can you elaborate some on what data is in which column or row?
more than likely as Powerdink said a countif, countifs or sumif, sumifs or a combination of these functions is going to do what you need here, it really depends on the data and how it's set up.

 

[scottylad2]

A sample of your layout will help. What do you mean by a count and not a sum of errors? Do you mean a separate count of errors per category or just the total number of errors?

 

[Purple_Girl]

Thanks for your response! I am trying now with a pivot table...looks like I might have to have multiple pivot tables for this. Here's what I've got so far:

http://tinypic.com/r/2liu71h/7

The only thing is that I've got to get the "count" of Total Errors (not the sum, just the number of cells with data in them) to match up with the infomration that I've got in the Pivot...do you know what I mean? Each error (each line with a number in the cell) matches a Category and both of those match a name. Do you have any ideas on how to tie in the Total Errors (only the ones with a number in the cell - not the sum)? This is driving me crazy!

 

[Purple_Girl]

FYI, that last response was for Powerdink...

Let me read the rest of your responses...thank you for your responses!

 

[Purple_Girl]

To "Cease" and "Scottylad2":

Sure, can you view this link - it shows columns/rows? It looks like this:

http://tinypic.com/r/2lia73m/7

So, for each cell that contains a number greater than "0" in the "TTL Errors" column, I need to count that row of data - not the sum. So, for each Name, I need to match the "count" of the TTL Errors (only if it is greater than "0") and for each line that has data in the TTL Errrors column, I need to match it to one of the Categories (Category column).

After I match up all of that data, then I will need to calculate each person's Quality %...which will be the number of TTL Errors made (cells with a number greater than "0") divided by the total number of categories...if that makes sense.

 

[Purple_Girl]

@Scottylad2: Yes, a separate count of TTL Errors per Category...and of course, per name...

 

[Cease]

Sorry I'm at work and am unable to pull up the link.

However the column you need to count, let's say the TTL column is in column B,
in c you can use, =countif(B:B,">0")
and that will count all the cells in that column greater than 0.

If you need that count for a certain individual, and their name is in column a:
If the worker's name is in cell a2, in cell c2 use:
=countifs(A:A,a2,B:B,">0") and it will count all of that person's name that is greater than 0 in column b. you can add another data setafterwards too, following the same pattern in the COUNTIFS function, where the first section is the range for criteria, and the next is the criteria itself.

Hope that makes sense, and/or helps some.

 

[Purple_Girl]

@Cease: Thank you SO much!!! This formula worked beautifully for capturing the instances of TTL Errors with a number greater than "0" and matching it to a name! :~)

Now, how would I match each error (that has a number greater than "0") with the Category to which it belongs??