Adding a secondary sheet into the equation

HI! I have the below formula which is pulling info from a sheet and giving me a percentage based on the p/f in a column. I have a third sheet that is the same info as the p/f sheet this is based on (technically the archive of this one) that i would like to add in as included in the percentage. So basically my formula needs to give me a percentage of passing based on two sheets. Di duplicate this somehow and continue it with the other sheet reference? How does the math work? Any insight would be wonderful, just not sure where to enter the second sheets references and how to get the math to add. I have a similar thing already happening but smartsheet built it about five years ago and the formula is wildly different lol

=IF(COUNTIFS({Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31)) = 0, 0, COUNTIFS({TTR Outcome}, "p", {Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31)) / COUNTIFS({Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31))) * 1

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Project Management

Accepted answers

All comments

Paul Newcome

Right now you have two COUNTIFS. It is basically broken down as

=COUNTIFS("p") / COUNTIFS(all)

To include the other sheet, you would need two more COUNTIFS built essentially the same except referencing the other sheet and then add them to their corresponding existing COUNTIFS.

=(COUNTIFS(1st "p") + COUNTIFS(2nd "p")) / (COUNTIFS(1st all) + COUNTIFS(2nd all))

Note the additional sets of parenthesis to make sure the addition of each is performed before the division part.

Finally (once you know this new part is working), you can use an IFERROR to output a zero if there are no rows for that employe (saves a bit of typing instead of an IF with two more COUNTIFS).

=IFERROR((COUNTIFS(1st "p") + COUNTIFS(2nd "p")) / (COUNTIFS(1st all) + COUNTIFS(2nd all)), 0)

amarkumar

@Katherine Camacho Prereq: create cross-sheet refs to the Archive columns, e.g. {TTR Outcome (Archive)}, {Employee Email TTR (Archive)}, {TTR Date (Archive)}

=IFERROR(( COUNTIFS({TTR Outcome}, "p", {Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31))+ COUNTIFS({TTR Outcome (Archive)}, "p", {Employee Email TTR (Archive)}, Employee@row , {TTR Date (Archive)}, >=DATE(2025, 1, 1), {TTR Date (Archive)}, <=DATE(2025, 1, 31)))/( COUNTIFS({Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31))+ COUNTIFS({Employee Email TTR (Archive)}, Employee@row , {TTR Date (Archive)}, >=DATE(2025, 1, 1), {TTR Date (Archive)}, <=DATE(2025, 1, 31))),0)

Paul Newcome

@amarkumar You may want to check / adjust your formatting…

Katherine Camacho

Hi! This make sense in general. so below is the first half of the equation.

Your saying add + and then the same as above starting at COUNTIFS and reference the other sheet. then the same concept on the back half?

Paul Newcome

@Katherine Camacho I'm saying forget the IF piece entirely, but I am not sure I completely understand by the last bit of your comment..

You are going to have a total of 4 COUNTIFS.

1st is the existing COUNTIFS counting the "p" on the primary sheet (keep existing employee and date range/criteria sets).

2nd will replicate the 1st, except all cross sheet references should be pointed at your archive sheet.

3rd is the existing COUNTIFS counting all of the rows on the primary sheet (keep existing employee and date range/criteria sets).

4th will replicate the 3rd, except all cross sheet references should be pointed at your archive sheet.

The final formula would end up being

=IFERROR((1st + 2nd) / (3rd + 4th), 0)

Katherine Camacho

I feel like im making it worse and getting farther from it lol. Or i just have commas and parenthesis where they shouldnt be.

This is countifs (og sheet email, date, date) then "p" + archive version / countifs (og email, date, date) + (archive version)

=COUNTIFS({Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31) "p") + COUNTIFS({Employee Email TTR}, Employee@row , {TTR Audit Archive Range 1}, >=DATE(2025, 1, 1), {TTR Audit Archive Range 1}, <=DATE(2025, 1, 31) "p")) / (COUNTIFS({Employee Email TTR}, Employee@row , {TTR Date}, >=DATE(2025, 1, 1), {TTR Date}, <=DATE(2025, 1, 31) + COUNTIFS({Employee Email TTR}, Employee@row , {TTR Audit Archive Range 1}, >=DATE(2025, 1, 1), {TTR Audit Archive Range 1}, <=DATE(2025, 1, 31))

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.