+ Reply to Thread
Results 1 to 13 of 13

If Countif Output Count Rank SUMPRODUCT

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Thumbs up If Countif Output Count Rank SUMPRODUCT

    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!
    Attached Files Attached Files
    Last edited by SunOffice; 09-01-2010 at 09:21 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: If Countif Output Count Rank

    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?
    Last edited by JBeaucaire; 08-30-2010 at 07:55 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If Countif Output Count Rank

    Cell G4:

    Please Login or Register  to view this content.

    Copy across and down.

    Regards

  4. #4
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Question Re: If Countif Output Count Rank SUMPRODUCT

    Quote Originally Posted by JBeaucaire View Post
    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?
    Quote Originally Posted by TMShucks View Post
    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!
    Attached Files Attached Files
    Last edited by SunOffice; 08-30-2010 at 09:51 PM.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: If Countif Output Count Rank SUMPRODUCT

    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.
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If Countif Output Count Rank SUMPRODUCT

    See attached workbook
    Attached Files Attached Files

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: If Countif Output Count Rank SUMPRODUCT

    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...

  8. #8
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Question Re: If Countif Output Count Rank SUMPRODUCT

    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!
    Last edited by SunOffice; 08-31-2010 at 10:27 PM.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: If Countif Output Count Rank SUMPRODUCT

    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)
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-31-2010 at 11:59 PM.

  10. #10
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: If Countif Output Count Rank SUMPRODUCT

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

    You guys are brilliant!!


    @Mr. JBeaucaire - got ur point easily!

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If Countif Output Count Rank SUMPRODUCT

    You're welcome. Thanks for the feedback.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If Countif Output Count Rank SUMPRODUCT

    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

  13. #13
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: If Countif Output Count Rank SUMPRODUCT

    Thanks again Mr. TMShucks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1