If Countif Output Count Rank SUMPRODUCT

[SunOffice]

Situation ??? We have some Employees/ Names in the column 'B', and the respective cell in the column 'A' shows abt the status of their work on the same task.

Aim ??? Now we would like to count their 'Completed' work status with the numbers so tht we can give them rank on their efficiency. If we come to know abt the exact 'Pending' work status, so we can easily know where we r running behind, and this count can help us to put penalty on those employees if they miss the target.
If the required result automatically comes up in a table then it'd be easier to paste data in the monthly master workbook and get the final monthly summary.

P.S. - It'd be better if anyone tell me how rank employees on their 'Completed' work status.

Note - Plz check the attached MS workbook for the details.

Thanks in Advance!

[JBeaucaire]

Put this formula in G4 and copy down/across the table to get the counts for each person and each category.

=SUMPRODUCT(--($C$2:$C$46=$F4), --($B$2:$B$46=G$3))

Now that you have that, what logic did you have in mind for ranking these employees based on this data?

Where on your sheet was this to occur? Column J?

[TMS]

Cell G4:

Please Login or Register  to view this content.

Copy across and down.

Regards

[SunOffice]

Put this formula in G4 and copy down/across the table to get the counts for each person and each category.

=SUMPRODUCT(--($C$2:$C$46=$F4), --($B$2:$B$46=G$3))

Now that you have that, what logic did you have in mind for ranking these employees based on this data?

Where on your sheet was this to occur? Column J?

Cell G4:

Please Login or Register  to view this content.

Copy across and down.

Thanks alot Mr. JBeaucaire and Mr. TMShucks!! U idea & formula really works.

Now can u guys plz check the updated MS workbook, I've applied ur formule and how can I use offset or some other function as the Name list with the Work Status can be longer in any day?? ( It totally depends on the per day volume of the work.)

For Ranking:
Every time I put filter then copy & paste, then give rank manually.

Aim ››› Can anyone plz tell me tht how to give rank automatically so the final result summary of the top performers can be displayed with their 'Total Earned Points' score?

Thanks in Advance!

[JBeaucaire]

A positional ranking formula in column K can give you the ranking based on scores in column J, breaking ties by the name that comes first in the list, so be sure to order the list in the order you would want to give tie breaks to.

=IF($J4="","",RANK($J4,$J$4:$J$16)+COUNTIF($J$3:$J3,$J4))

Then those values can be used to create your table down below.

[TMS]

See attached workbook

[JBeaucaire]

I couldn't recommend the use OFFSET or array formulas when they can be avoided, both are volatile and add overhead to the sheet unnecessarily. My two cents...

[SunOffice]

Thanks a world for ur wonderful help Mr. JBeaucaire and Mr. TMShucks!!

Perfect!!

I've just merged both the problem solving methods in two separate worksheets of a single workbook... plz check below attached MS Workbook.

@ Mr. TMShucks - Why did u put { } (Ctrl+Shift+Enter) on the formula in the cell 'K4' for Ranking?

Please Login or Register  to view this content.

Question:
What if in these data worksheets when we get more numbers of tasks, then we have to make a huge list in the column A and B. B'cuz we don't know how much work will we get on today/ tomorrow/next to tomorrow. so the size (length) of Column A and B will be increased or decreased per day.

...so I need the result/ output, which shows according to the column A and B size.

Could u plz help me out in this too... ??

Thanks in Advance!

[JBeaucaire]

My sheet uses formulas that are coded to the length of your data. Lengthen those ranges to enough rows to cover your longest expected days...maybe 200 row is enough?

I used CTRL-H to search for $46 and replace with $200 (Options: Search in Formulas...Replace All)

[SunOffice]

Thanks a bundle Mr. JBeaucaire and Mr. TMShucks!!

You guys are brilliant!!

@Mr. JBeaucaire - got ur point easily!

[TMS]

You're welcome. Thanks for the feedback.

[TMS]

The formula, entered with Ctrl-Shift-Enter and designated by {...} is called an array function or array entered formula.

Some examples are quite complex and I honestly do not know how they are arrived at.

However, a simple example:

Let's say you have numbers in cells A2 to A7 and B2 to B7. You need to multiply each pair together and sum the total. So, in cell C2, you could put the formula =A2*B2 and drag this down the column to cell C7. And, in cell C8, you could put the formula =SUM(C2:C7) ... and get your answer.

You could though, array enter the formula: =SUM(A2:A7*B2:B7) and get the same result with no interim or helper column.

It depends on whether or not you want or need to see the intermediate results. I'm not sure if there are any pointers regarding the efficiency, or not, of using this method.

Personally, I tend to think of using SUMPRODUCT before array entered formulae. For example, =SUMPRODUCT((A2:A7),(B2:B7)) or =SUMPRODUCT((A2:A7)*(B2:B7)) will produce the same result without being array entered.

DonkeyOte has a reference to a URL on SUMPRODUCT which you might find interesting.

Regards

[SunOffice]

Thanks again Mr. TMShucks!!