Index Formula with CountIFS Running Total

Hi - trying to make a Training Matrix for my company.

"Matrix" sheet has the following columns:
Row: (Auto-Number)
Doc ID: (Text = document needing to be trained on)
Employee 1: Check Box if they need to be trained
Helper: column formula to give running count of checked boxes

=COUNTIFS([Employee1]:[Employee1], [Employee1]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row, [Employee1]:[Employee1], 1)

Can someone please help me with an INDEX function column formula (or another solution) to place on another sheet to yield just the Doc IDs from "Matrix" that "Employee 1" is checked (=1) OR "Helper" > 1 (per the Helper column formula)

I have cross-sheet references {Doc ID}, {Employee1}, {Helper}

Find more posts tagged with

Formulas

Countif

Accepted answers

KPH

Like this?

You can use an INDEX COLLECT, like this:

=IFERROR(INDEX(COLLECT({Doc ID}, {Employee1}, 1, {Helper}, Number@row), 1), "")

All comments

KPH

As the only boxes that have a Helper > 1 also have Employee 1 checked, do you need to include the OR logic? There won't be rows where Employe1 is not checked and Helper is >1 so, would the Doc IDs where Employee 1 is checked be sufficient?

How do you want the IDs to appear on the second sheet? One cell with them all in? One row per Doc ID?

Leeweber

KPH - sorry if the "or" was misleading, I do not require the OR logic, just thinking whichever is easier to build into the formula.

I'd like the IDs to appear as one row per Doc ID :)

KPH

Like this?

You can use an INDEX COLLECT, like this:

=IFERROR(INDEX(COLLECT({Doc ID}, {Employee1}, 1, {Helper}, Number@row), 1), "")

Leeweber

Yes, that's great, thank you! I updated the auto-number column to RowID and created an additional RowNo column with formula =MATCH(RowID@row, RowID:RowID, 0) so if I add a line to the middle of the source sheet, the order will match in my destination sheet.

Thanks again, KPH!

KPH

Looks good. Glad I could help.

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.