I have a summary sheet that counts the number of not started, in progress and done tasks. This summary sheet looks up the name (index, collect contains) of the parent (Epic) of the tasks (Stories). Think Epics to Stories. The summary sheet has a column of Epic Names and a summary of not started, in progress and open stories under that Epic Name on the primary sheet. There will be multiple primary sheets (different workstreams) has 1,000's of rows with many Epics and Stories. Each primary sheet will have a different number of Epics on it's sheet. One primary sheet could have 25 Epics while another may have 50 Epics. I would like to be able to have a summary sheet template that I can use to be ablle to use the Index, Collect, Contains "Epic1" next row Index, Collect, Epic2 .... and so on for each row up to Epic100 - when I do that if the summary sheet has 12 Epics, as an example I get Invalid Value after Epic12. I want to be able to turn that Invalid Value into a 0 so when my column calculating Not Started isn't Invalid Data but 0.
Here is the formula I use for Index Collect.
=INDEX(COLLECT({S4 Hana WS Template EpicName 1}, {S4 Hana WS Template EpiqUniq 1}, CONTAINS("Epic1", LOWER(@cell))), 1) - Where "Epic1" is changed to Epic2 on the next row - down to Epic100
On the Primary sheet I created a uniq epic name - Epic1, Epic 2 etc.
In the Not Started column and for each other dimension, In Progress, Completed etc. I use the formula.
=COUNTIFS({S4 Hana WS Template EpicName 1}, $EpicName@row, {S4 Hana WS Template UT Status 2}, ""Not Started")
Epic Name Column formula
Countif formula first summary column
