Re: What is the order of operations for nested IF & AND statements over a range of cells

anl299 

As variant for the legacy Excel in F9

=IFERROR(
    INDEX($2:$2,
        AGGREGATE(15,6,
            COLUMN($B$2:$K$2)/($B$4:$K$4=0)/($B$6:$K$6<>0),
            ROW()-ROW($F$8))
    ),
"" )

and drag it down. No Ctrl+Shift+Enter is required.

On Excel 365 or 2021

=TOCOL( FILTER(category, (score=0)*(weight<>0) ) )

anl299 

In my first reply i unfortunately forgot to mention that the formula must be dragged down from cell E9. In my sample file i entered the formula in cell E9 and confirmed it with ctrl+shift+enter. Then i dragged the formula down across range E9:E18. Since there are 4 categories with a weight and no score (in the sample file) these are now returned in range E9:E12. Cells E13:E18 are empty because the formula uses the IFERROR function to return an empty cell instead on an error message. If the entries for the weight and score are changed for a category the results update dynamically in range E9:E18. If all 10 categories have a weight and no class then all are listed in range E9:E18.

Do you work with a recent version of Excel such as Excel 2021, Office 365 or Excel for the web? If so then there are other possible solutions for this task with formulas such as TEXTJOIN and FILTER.