Countifs a column with values and Invalid value error

Bruce Johnson

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

David Joyeuse

HI @Bruce Johnson

Just wrap your INDEX/COLLECT formula within an IFERROR.

=IFERROR(INDEX(COLLECT({S4 Hana WS Template EpicName 1}, {S4 Hana WS Template EpiqUniq 1}, CONTAINS("Epic1", LOWER(@cell))), 1) , 0)

That will return a 0 if there's an error.

I'm not getting why you use the INDEX function here, it's kinda pointless to me with a COLLECT function.

Last, I'm wondering why do you use the LOWER function for the CONTAINS one and have "Epic1" as the value you're looking for. If you compare Epic1 to LOWER(@cell) it should never work because of the "E" if I'm right as I believe CONTAINS is case sensitive (may be wrong here).

Hope it helped!

Ramzi K

@Bruce Johnson

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

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.

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?

David Joyeuse

HI @Bruce Johnson

Just wrap your INDEX/COLLECT formula within an IFERROR.

=IFERROR(INDEX(COLLECT({S4 Hana WS Template EpicName 1}, {S4 Hana WS Template EpiqUniq 1}, CONTAINS("Epic1", LOWER(@cell))), 1) , 0)

That will return a 0 if there's an error.

I'm not getting why you use the INDEX function here, it's kinda pointless to me with a COLLECT function.

Last, I'm wondering why do you use the LOWER function for the CONTAINS one and have "Epic1" as the value you're looking for. If you compare Epic1 to LOWER(@cell) it should never work because of the "E" if I'm right as I believe CONTAINS is case sensitive (may be wrong here).

Hope it helped!