Nest IF with OR condition

[Galena1]

So it's been a while since I've done a nested IF statement and things are kinda fuzzy:

I need help creating an IF statement that captures three (3) conditions:

1. If there is a values in either cols D OR E (i.e. 'Completed' and 'Completed (Equivalent')) AND no other columns, then Status in col M = 'All Completed'
2. If there are values in either cols D OR E AND any other column, then Status in col M = 'Some Completed'
3. If there are values in any column EXCEPT cols D OR E then Status in col M = 'None Completed'


Anyone out there got a simple formula?

 

[Joe4]

When you say "if there are values in ...", do you really mean non-zero values (because all of your examples have values!)?
And should the Grand Total column be excluded from these calculations (as it appears to just be adding the values in columns B-L)?

 

[Fluff]

Will the cells just have 0 or 1, or can they contain other numbers?

 

[Galena1]

Will the cells just have 0 or 1, or can they contain other numbers?

Yes, since it's a pivot table of a larger dataset, there could be values other than 0 and 1.

 

[Galena1]

When you say "if there are values in ...", do you really mean non-zero values (because all of your examples have values!)?
And should the Grand Total column be excluded from these calculations (as it appears to just be adding the values in columns B-L)?

I forgot mention this is a pivot table and I forgot to suppress the null cells and replace with blanks, so you see 0's.
And yeah. I don't care about evaluating the GT column. Just ignore.

 

[Fluff]

Not sure if this will work with a pivot table, but try

=IF(SUM(D2:E2)=0,"None Completed",IF(SUM(B2:C2,F2:K2)>0,"Some Completed","All Completed"))

 

[Galena1]

So it's been a while since I've done a nested IF statement and things are kinda fuzzy:

I need help creating an IF statement that captures three (3) conditions:

1. If there is a values in either cols D OR E (i.e. 'Completed' and 'Completed (Equivalent')) AND no other columns, then Status in col M = 'All Completed'
2. If there are values in either cols D OR E AND any other column, then Status in col M = 'Some Completed'
3. If there are values in any column EXCEPT cols D OR E then Status in col M = 'None Completed'


Anyone out there got a simple formula?View attachment 42467

So it's been a while since I've done a nested IF statement and things are kinda fuzzy:

I need help creating an IF statement that captures three (3) conditions:

1. If there is a values in either cols D OR E (i.e. 'Completed' and 'Completed (Equivalent')) AND no other columns, then Status in col M = 'All Completed'
2. If there are values in either cols D OR E AND any other column, then Status in col M = 'Some Completed'
3. If there are values in any column EXCEPT cols D OR E then Status in col M = 'None Completed'


Anyone out there got a simple formula?View attachment 42467

Here's a better representation of the data without the '0's':

 

[Galena1]

Not sure if this will work with a pivot table, but try

=IF(SUM(D2:E2)=0,"None Completed",IF(SUM(B2:C2,F2:K2)>0,"Some Completed","All Completed"))

Thanks! That works great.

 

[Fluff]

Glad we could help & thanks for the feedback.