Forum Discussion
Solved
IF AND statement need to omit blanks
I currently have a document that looks like the above. Basically, if certain cells are a 2, then the column AC will turn into a 2 should certain criteria be met. If the criteria are not met, it is a 1.
However, with this, it then fills all of the column AC with a 1 even though there is no data in rows 6, 7, 8 etc.
My current IF statement reads as below but how would I amend that to have the cell stay blank if none of that row is filled e.g. if no data was in L6, M6, N6 etc, how could AC6 remain a blank cell and not be populated with a 1.
=IF(AND(L6=2,M6=2,N6=2,O6=2,P6=2,Q6=2,R6=2,S6=2,T6=2,U6=2,V6=2,W6=2),2,1)
Any help much appreciated.
=IF(COUNTBLANK(L4:W4)=12,"",IF(AND(L4=2,M4=2,N4=2,O4=2,P4=2,Q4=2,R4=2,S4=2,T4=2,U4=2,V4=2,W4=2),2,1))This returns the intended output in my example if i correctly understand what you want to do.
5 Replies
- Sergei,
2 problems, you have a solution to both. Thank you.MDawson2025 , you are welcome
=IF(COUNTBLANK(L4:W4)=12,"",IF(AND(L4=2,M4=2,N4=2,O4=2,P4=2,Q4=2,R4=2,S4=2,T4=2,U4=2,V4=2,W4=2),2,1))This returns the intended output in my example if i correctly understand what you want to do.
- This is superb. Thank you so much.
