Asking my question will make more sense if I just show you what I’m trying to do. In the picture below you can see the different categories for classes. What I want is just a box (offscreen) that lists every category that has no grades BUT is still being used to calculate the final grade. For example, column K, the one labeled Final should be listed because row 4 has 0 in it but row 6 has a non zero value. Columns #06-#09 have nothing in row 4, BUT since they all have zero in row 6 they should be ignored.

If I use nested IF and AND functions, the logic test looks like this:

IF(AND(B4:K4=0, B6:K6<>0), true, false)

The problem is, it doesn’t work. I think it reads the whole range of B through K (row 4) checks if they’re all equal to zero, and then it reads the entire range of B through K (row 6), K6 returns false and it’s done. The outer IF statement only runs a single time, checking every box in both rows simultaneously.

Is there a way for me to get the if/and statement to check B4 and B6 together, then C4 and C6 together, then D4 and D6…

Also, I do not want to individually select every cell, I would much rather use ranges.

Thanks, if you’re not sure you understand what I’m saying let me know and I will try to explain differently or show more of my excel logic.

​Asking my question will make more sense if I just show you what I’m trying to do. In the picture below you can see the different categories for classes. What I want is just a box (offscreen) that lists every category that has no grades BUT is still being used to calculate the final grade. For example, column K, the one labeled Final should be listed because row 4 has 0 in it but row 6 has a non zero value. Columns #06-#09 have nothing in row 4, BUT since they all have zero in row 6 they should be ignored.If I use nested IF and AND functions, the logic test looks like this:IF(AND(B4:K4=0, B6:K6<>0), true, false) The problem is, it doesn’t work. I think it reads the whole range of B through K (row 4) checks if they’re all equal to zero, and then it reads the entire range of B through K (row 6), K6 returns false and it’s done. The outer IF statement only runs a single time, checking every box in both rows simultaneously.Is there a way for me to get the if/and statement to check B4 and B6 together, then C4 and C6 together, then D4 and D6…Also, I do not want to individually select every cell, I would much rather use ranges. Thanks, if you’re not sure you understand what I’m saying let me know and I will try to explain differently or show more of my excel logic.  Read More

​