How to Count a Column if it matches certain data

[hartjohnson]

Ok, I am creating a searchable sheet. Here is what I have and need.

-2 Sheets Total
-This is doing Calculations of inputted scores. A single person can have multiple scores inputed on different days
-I need columns B&C in the Total Scores sheet to automatically Sum the total scores from each person, so there are multiple results. Meaning that if Jordan Hill has 2 entries in the Entries sheet, with influence scores of 1 and 1,, then I need Column B to display 2 next to her name.

I hope this makes sense, I am totally lost. I tried combining Index and Match functions in an array but i can't get it to get past the first result. Help please


Example Workbook.xlsx

[berlan]

Hi and welcome to the board!

Try (in the second sheet "Total Scores"),

B2: =SUMIF(Entries!$C:$C,'Total Scores'!$A2,Entries!$S:$S)

C2: =SUMIF(Entries!$C:$C,'Total Scores'!$A2,Entries!$T:$T)

and copy down.

Hope that helps,
berlan

[hartjohnson]

Wow, I feel pretty dumb for not thinking of that....Thank you so much! That appears to be working!

[berlan]

You're welcome.

[hartjohnson]

Maybe you could help me again. Attached is another workbook. What I am trying to do is count the number of reviews that a given person gets. So in the sheet 'Calculations', Column C should calculate how many reviews the person in Column A has from the sheet 'Reviews'. I have it doing something, but it won't count the data right. Then if I drag the formula down, it is counting people that don't have reviews...Help..

[FDibbins]

hartjohnson, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

(try this...=COUNTIF(Reviews!C:C,Calculations!A2))

[hartjohnson]

Nope, didn't work for what i need to do. Let me update the workbook, might help explain it better.

[hartjohnson]

Attached is the new workbook. Basically I have different criteria that is scored. Front counter, breakfast, and others. Each on can be scored 1-5 or left blank. What i need is for the calculation to count the # of scores in each category, based on the persons name. This would display that number in the cell.

[FDibbins]

the formula you had for Front can be copied across for all other columns...
=SUMIF(Reviews!$C:$C,$A2,Reviews!F:F)
copied across becomes...
=SUMIF(Reviews!$C:$C,$A2,Reviews!G:G)

etc

[hartjohnson]

Right, I got that part, it is the next column over that is not working properly. The formula you gave me does count the number of entries, but it counts the total number of entries based on name. What I need it to do is count each Category individually. For example. Jordan Hill could have 3 separate reviews. However, one of those reviewers does not have to fill in each category. So even though she has 3 total reviews, she could potentially have only 2 scores in a given category. I updated the workbook and highlighted the column that I am having trouble with in Yellow.

[FDibbins]

For example. Jordan Hill could have 3 separate reviews. However, one of those reviewers does not have to fill in each category. So even though she has 3 total reviews, she could potentially have only 2 scores in a given category.

OK, you lost me there I can only work on the data you provided, and in that data, that name appears 3 times. I dont see how you can have the name appear 3 times, but only count it twice - which 1 are you not counting, and why?

[hartjohnson]

Sorry, hard to explain something like this. Let me try again.

My column headers on one sheet are name, front counter, breakfast, etc. Under name it is the employees name, then front counter has a score 1-5, breakfast has a score 1-5, etc. Basically the managers are able to give the employee a review. Lets say Joe gives Jordan a 5 on the front counter and 5 on breakfast. Mary can review Jordan as well but she only gives her a score of 4 on the breakfast portion. So potentially you could have multiple "reviews" without filling in each category.

What I have to do is find the sum of each category, which I did. But then I need the sheet to automatically tell me how many scores that person received on that particular section. Using the example, It would return a total of 5 for Jordan on Front Counter, with a count of 1 score. While breakfast would have a total of 9 with a count of 2 scores. This would then allow me to calculate the average for their scores in each section.

The workbook that I attached in my previous post has the calculations in the area I need them, I just can't figure out how to make the column Count the number of scores for each section based on that persons name.

So basically, I want column C on my Calculations sheet to count the number of scores for that given person only in column F of my reviews sheet ONLY for that particular person. Hope that makes sense.

[FDibbins]

you need to refer to the same sheet names as are in your sample workbook (Reviews and Calculations)
Also, "breakfast would have a total of 9 with a count of 2 scores" how do you get 9?? and where does the 2 come in?

Are you still refering to the same WB that you posted in post #10?

[oeldere]

With a pivot table.

See the attached file.

[hartjohnson]

you need to refer to the same sheet names as are in your sample workbook (Reviews and Calculations)
Also, "breakfast would have a total of 9 with a count of 2 scores" how do you get 9?? and where does the 2 come in?

Are you still refering to the same WB that you posted in post #10?

Yes same WB as #10.

My written example of a sample of scores means that the breakfast category would have a Sum of 9 because the 5 score from Joe and the 4 score from Mary. The count for breakfast, so the number of scores that Jordan got in the breakfast category would be 2.

[hartjohnson]

Ok, that could could work oeldere. My only concern would be if I added or deleted employees? Would the table automatically update if I added a new employee below the last one? Or would I just make the pivot table range fill more rows than there are employees?

[hartjohnson]

Solved. The Pivot table will work. I had to use a secondary sheet to pull the data from so I could do a few more calculations, but it is working flawlessly now. Thank you all!