Forum Discussion

Marcus_Booth's avatar
Marcus_Booth
Brass Contributor
May 19, 2025

Formula needed to retrieve % of correct reviews for an employee, where reviews are on separate tabs.

Hello everyone! Thank you in advance for any assistance you are able to offer. 

I'm working in MS 365 for web on a windows cloud environment through a Citrix network.

Looking for a formula that will populate one employee's evaluation results as a % correct for each of multiple worksheet tables of reviewed items (pass/fail). 

See attached workbook for details on what I'm trying to do.

I would like to be able to choose an employee's name from a drop down on the RESULTS tab, select the months to begin and end a date range and have this return the total reviews and number failed for each 'Review Name' for that employee. 

Thanks again! :) 

excel
Excel for web
Formulas and Functions

11 Replies

Sort By
  • Marcus_Booth's avatar
    Marcus_Booth
    Brass Contributor
    May 28, 2025

    I had marked a reply here as being the solution, but it by accident. Now it's disappeared and I can't find the post from HansVogelaar​ to re-mark it as the solution. 

  • Marcus_Booth's avatar
    Marcus_Booth
    Brass Contributor
    May 21, 2025

    Any ideas about what would work with multiple worksheets? I'm wondering if I can use the FILTER function somehow. It would be nice to be able to select an employee from a drop-down list, choose the start and end dates as a range, and have the results displayed for each review (worksheet) that was completed for that employee. 

    I've tried to figure out a way to use Pivot Tables, but I haven't been successful. There must be some way to do this, but I'm at a loss. 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 21, 2025

      You wrote in the original post "See attached workbook for details on what I'm trying to do."

      I don't see an attachment, however. If you are not able to attach a file, could you make it available through OneDrive, Google Drive, Dropbox or similar?

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    May 20, 2025

    Suppose you can consider the following:

     

    1. Retrieve the total number of reviews conducted within the selected date range.
    2. Count the number of failed reviews.
    3. Compute the percentage of correct reviews with this formula:


    Correct % = (Total Reviews - Failed Reviews) / Total Reviews * 100

     

    Given that your data is spread across multiple worksheets, you can use SUMIFS or COUNTIFS functions to gather relevant information for an employee within the specified date range:

    = (SUMIFS('Sheet1:SheetN'!B:B, 'Sheet1:SheetN'!A:A, Results!A2, 'Sheet1:SheetN'!C:C, "Pass", 'Sheet1:SheetN'!D:D, ">=" & StartDate, 'Sheet1:SheetN'!D:D, "<=" & EndDate))
/
(SUMIFS('Sheet1:SheetN'!B:B, 'Sheet1:SheetN'!A:A, Results!A2, 'Sheet1:SheetN'!D:D, ">=" & StartDate, 'Sheet1:SheetN'!D:D, "<=" & EndDate))
* 100

     

Resources

OSZAR »