Apologies, If the solution is already available, I have been searching for hours!

The problem I am facing is that to put conditional formula based on column ranges for e.g:

Conditions:

1) A1=0, B1=0, C1=0, D1=0 = Result Last Four columns are zero
2) A1=1, B1=0, C1=0, D1=0 = Result Last Three columns are zero
3) A1=1, B1=1, C1=0, D1=0 = Result Last Two columns are zero
4) A1=1, B1=1, C1=1, D1=0 = Result Last column is zero
5) A1=0, B1=0, C1=1, D1=0 = Result Last column is zero
6) A1=0, B1=1, C1=0, D1=0 = Result Last Two columns are zero
7) A1=0, B1=0, C1=0, D1=1 = Result Last Column is Not Empty

The catch in the above results is that the condition should consider the column zeros from right to left.

CodePudding user response：

You can:

1. Create a named range (I will call it Sentences) to store the hardcoded sentences you want as a result. Below is an example (do not include the header in the named range):

2. Apply this array formula (to be validated using Ctrl Shift Enter):
   =INDEX(Sentences, 1 SUM(2^(COLUMNS(A1:D1)-COLUMN(A1:D1))*(A1:D1<>0)))

Explanation: the formula counts empty cells as bits (weights: 1, 2, 4, 8), adds the bits together to create and index then look into the named range the sentence at the calculated index.

Obviously, you will have to edit the values from the named range if the values I typed do not suit your need.

Edit: You can also handle only special cases in the range and leave the general case to a more generic formula.
By special cases, I mean things like "Last column is not empty" (only applies to the last column) as opposed to "One column is zero". (applies to all columns).

You can just leave some cells of the named range empty whenever it is a general case and let the formula below do the job.

=IF(
LEN(INDEX(Sentences, 1 SUM(2^(COLUMNS(A1:D1)-COLUMN(A1:D1))*(A1:D1<>0)))) > 0,
INDEX(Sentences, 1 SUM(2^(COLUMNS(A1:D1)-COLUMN(A1:D1))*(A1:D1<>0))),
INDEX({"All columns are zero","Three columns are zero","Two columns are zero","1 column is zero"}, 1 COUNT(IF(A1:D1=0,1)))
)