Countifs a column with values and Invalid value error

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


Bruce Johnson

Director Portfolio, Project Methods & Governance

Veolia North America

Boston, MA

Best Answer

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    edited 10/07/20

    @Bruce Johnson

    What is the formula in the cells that have the #INVALID error?

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    Hi Ramzi


    As can be seen in the Epic Name Column formula screenshot you can see the last row that is Invalid Value. ty.

    Bruce Johnson

    Director Portfolio, Project Methods & Governance

    Veolia North America

    Boston, MA

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Bruce Johnson

    The reason you're getting an INVALID value is because your formula is referring to a cell with an error in it.

    See if you can correct the errors in your second row first.

    What is the formula in those error cells in the second row?

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!