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}
Best Answer
-
Like this?
You can use an INDEX COLLECT, like this:
=IFERROR(INDEX(COLLECT({Doc ID}, {Employee1}, 1, {Helper}, Number@row), 1), "")
Answers
-
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?
-
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 :) -
Like this?
You can use an INDEX COLLECT, like this:
=IFERROR(INDEX(COLLECT({Doc ID}, {Employee1}, 1, {Helper}, Number@row), 1), "")
-
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!
-
Looks good. Glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!