1 Fn, 2 Logical tests, 1 True value, 2 False values

**ryanchry** · 01-03-2012, 02:17 PM

Example:
Commission A: 250
Commission B: 150
Commission C: 100

Commission X: 200
Commission Y: 150

I have the following combinations... You can receive...A+B+C, OR X, OR Y

I'm trying to say... IF Cells X and Y are Blank then A+B+C is true. If Cells X OR Y are filled then = X or Y depending on which is filled...

I've tried myself and failed...I currently have IF(AND(X="",Y=""),A+B+C,X) but that only satisfies if X is filled. I want Y to show up if it is filled instead of X

Or is there a better way to write what i am tring to accomplish?

Help!! and Thank you!

**Ron Coderre** · 01-03-2012, 02:39 PM

If your sample data is in A1:B5

Commission A:	250
Commission B:	150
Commission C:	100
Commission X:	200
Commission Y:	150

This regular formula:

=CHOOSE(SUMPRODUCT((B4:B5<>"")*{1;2})+1,SUM(B1:B3),B4,B5,"both x and y")

Returns:
A+B+C: if X and Y are blank
X: if only X and not Y
Y: if only Y and not X
"both x and y": if X and Y have values.

Is that something you can work with?