If function

[Kelleyscott]

I would like to know what is the full IF function with regards to the above screen shot.

If all cells B1:B5 are noted as "Joint" then the B6 result = "Joint"
If all cells B1:B5 are noted as "Sole" then the B6 result = "Sole"

However if any one of B1, B2, B3, B4, B5 is Joint or Sole (therefore B1:B5 are not all the same) then I require the outcome to be "Joint/Sole"

I was hoping there was a shorter IF function rather than building out a long one with all variations?

 

[Austin Lang]

Try this:

 

[Kelleyscott]

thank you Austin Lang Much appreciated

 

[Peter_SSs]

You could also use this shortened version of the suggested formula.

=IF(AND(EXACT(B1:B5,B1)),B1,"Joint/Sole")

Further, I suspect that upper/lower case would not matter (or is not possible) and if that is the case, no need for EXACT either.

=IF(AND(B1:B5=B1),B1,"Joint/Sole")

 

[bebo021999]

For Excel version earlier than 365 (or maybe, earlier than 2019?), array formula in #4 need to be confirmed by Ctrl-Shift-Enter
Another version, non-array formula:

=IF(COUNTIF(B1:B5,B1)=5,B1,"Joint/Sole")

or longer a bit:

=LOOKUP(COUNTIF(B1:B5,"Joint"),{0,1,5},{"Sole","Joint/Sole","Joint"})

 

[Alex Blakenburg]

In terms of non-array formulas probably a bit more robust but longer.

=IF(COUNTIF(B1:B5,B1)=(COUNTA(B1:B5)+COUNTBLANK(B1:B5)),B1,"Joint/Sole")

OR

=IF(COUNTIF(B1:B5,B1)=(ROW(B5)-ROW(B1)+1),B1,"Joint/Sole")

 

[Kelleyscott]

Try this:

View attachment 62885

Hi Austin,
This formula doesn't work if B4 and B5 are empty. In some instances there may not be 5 Accounts each time. There may be only 1 or up to 5.
WIth this formula if B1, B2 & B3 are "Joint" and B4 and B5 are blank - the result comes out at Joint/Sole.... which is incorrect.

 

[Peter_SSs]

WIth this formula if B1, B2 & B3 are "Joint" and B4 and B5 are blank - the result comes out at Joint/Sole.... which is incorrect.

.. but it is what you said in post 1 that you wanted.

therefore B1:B5 are not all the same) then I require the outcome to be "Joint/Sole"

In what you have shown and described in post #7, B1:B5 are not all the same. That is why all (I think) suggestions in the the thread will produce "Join/Sole" for this latest example.

You also stated the following, which also implies that if all B1:B5 are not "Joint" then the result would not be "Joint"

If all cells B1:B5 are noted as "Joint" then the B6 result = "Joint"

So, it seems your original description is not what you actually wanted.

Looks to me now what you really want is the formula in B6 below?
I have copied it across to show results for various data samples.

22 04 21.xlsm
	B	C	D	E	F	G	H
1	Joint		Joint				Joint
2	Joint	Sole	Joint			Sole	Joint
3		Sole	Joint				Sole
4		Sole	Joint		Joint		Joint
5			Joint
6	Joint	Sole	Joint		Joint	Sole	Joint/Sole
Joint

Cell Formulas
Range	Formula
B6:H6	B6	=LET(f,UNIQUE(FILTER(B1:B5,B1:B5<>"","")),IF(ROWS(f)=1,f,"Joint/Sole"))