Help with complex What IF Formula to calculate individuals Pass Percentage each month

Hello! I am not sure where the issue is with the formula below. I am trying to get the percentage pass rate for an individual by counting the Pass within the months time frame and then dividing that by the sum of that same individuals count for the same time frame.

I have tried parenthesis and continue getting either invalid argument or unparsable. Any help is appreciated!

Formula for calculating April: =(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, {NA: Individual Performance Range 1}, "Pass", {NA: Individual Performance Range 2}, <=DATE(2023, 4, 31), {NA: Individual Performance Range 2}, >=DATE(2023, 4, 1))) / (COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, {NA: Individual Performance Range 2}, <=DATE(2023, 4, 31), {NA: Individual Performance Range 2}, >=DATE(2023, 4, 1)))

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

You have some local references mixed with cross sheet references within the same COUNTIF, that will not work. Based on the references, I am assuming you are pulling data from another sheet by matching it against [Assigned To]?

Without seeing the actual sheets, this is my best guess:

=COUNTIFS({Assigned To}, [Assigned To]@row, {NA: Individual Performance Range 1}, "Pass", {NA: Individual Performance Range 2}, <=DATE(2023, 4, 31), {NA: Individual Performance Range 2}, >=DATE(2023, 4, 1)) / COUNTIFS({Assigned To}, [Assigned To]@row, {NA: Individual Performance Range 2}, <=DATE(2023, 4, 31), {NA: Individual Performance Range 2}, >=DATE(2023, 4, 1))

Elizabeth Smiley23

Hey, I was trying to cross reference. I've combined the data so all calculations are being done in one sheet but am now getting #dividedbyzero

Formula

=COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, [GAO Guidance Pass/Faill]:[GAO Guidance Pass/Faill], "Pass", Date:Date, <=DATE(2023, 4, 31), Date:Date, >=DATE(2023, 4, 1)) / COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Date:Date, <=DATE(2023, 4, 31), Date:Date, >=DATE(2023, 4, 1))

Eric Law

@Elizabeth Smiley23 the reason you are getting that error is because it's not counting any assignments for that person during that period. I would recommend putting an IFERROR statement that just says, "No Assignments Found".

=IFERROR(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, [GAO Guidance Pass/Faill]:[GAO Guidance Pass/Faill], "Pass", Date:Date, <=DATE(2023, 4, 31), Date:Date, >=DATE(2023, 4, 1)) / COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Date:Date, <=DATE(2023, 4, 31), Date:Date, >=DATE(2023, 4, 1)),"No Assignments Found")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.