column formular?

Christine Menke

Hi Experts,

I'm using this formula to calculate how many rounds I have done.

=COUNTIFS(Season$1:Season@row, Season@row, [Dev Type (Article)]$1:[Dev Type (Article)]@row, [Dev Type (Article)]@row, Factory$1:Factory@row, Factory@row, [Article#]$1:[Article#]@row, [Article#]@row)

It works, if I copy it in each new row, but I would like, that whenever a new row is added, that this formula is cascaded as well.

Any idea how, I could modify it and change it to a column formula?

Thanks

Paul Newcome

Insert an auto-number column (no special formatting required) called "Auto". Then insert a text/number column called "Row" and insert this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you would adjust your COUNTIFS like so:

=COUNTIFS(Season:Season, Season@row, [Dev Type (Article)]:[Dev Type (Article)], [Dev Type (Article)]@row, Factory:Factory, Factory@row, [Article#]:[Article#], [Article#]@row, Row:Row, @cell <= Row@row)

Christine Menke

Christine Menke

Thanks for your help @Paul Newcome !

Paul Newcome

Happy to help. 👍️

Paul Newcome

Looks like you just need an IFERROR to get rid of the error.

=IFERROR(INDEX({SS24 CWA Running DEV Type (article)}, MATCH([Article#]@row, {SS24 CWA Running Range 1}, 0), 1), "")

Paul Newcome

Insert an auto-number column (no special formatting required) called "Auto". Then insert a text/number column called "Row" and insert this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you would adjust your COUNTIFS like so:

=COUNTIFS(Season:Season, Season@row, [Dev Type (Article)]:[Dev Type (Article)], [Dev Type (Article)]@row, Factory:Factory, Factory@row, [Article#]:[Article#], [Article#]@row, Row:Row, @cell <= Row@row)

Christine Menke

Christine Menke

Thanks for your help @Paul Newcome !

Paul Newcome

Happy to help. 👍️

Christine Menke

Hi @Paul Newcome

May I ask your for help again.

I have changed the Dev Type ( Article ) column to a index match formula.

In some cell the result is "No Match" which is okay.

But the result on below formula is now "No match" for all cells.

=COUNTIFS(Season:Season, Season@row, [Dev Type (Article)]:[Dev Type (Article)], [Dev Type (Article)]@row, Factory:Factory, Factory@row, [Article#]:[Article#], [Article#]@row, Row:Row, @cell <= Row@row)

I guess I need to adjust the formula for Dev Type (article) correct?:

=INDEX({SS24 CWA Running DEV Type (article)}, MATCH([Article#]@row, {SS24 CWA Running Range 1}, 0), 1)

Would be great, if you could help me once more :-)

Thanks

Paul Newcome

Looks like you just need an IFERROR to get rid of the error.

=IFERROR(INDEX({SS24 CWA Running DEV Type (article)}, MATCH([Article#]@row, {SS24 CWA Running Range 1}, 0), 1), "")