Excel - Formula using priority rules depending on range inputs

0
votes

This is probably a simple fix (although me thinking this, means it probably isn't), so I apologise in advance if this is mere child's play.

In an excel sheet I am working on, I have a range (for the sake of this example is A1:A10) which can contain one of 3 variables (not including blanks) - A, B or C.

I require a formula in another cell to review the range in question and output a value based on the following rules in this priority:

  1. If A appears anywhere in the column, regardless of other inputs, display A;
  2. If B and C appear in the column, display A;
  3. If only B appears in the column, display B;
  4. If only C appears in the column, display C; and
  5. If all cells within the column are blank, display blank

For rules 1-4, any blank cells within the column should not be considered. It is only where all cells are blank, i.e. rule 5, that this should be considered.

I have tried IF formulas but have found these only consider a single cell. Also I have attempted using SUMPRODUCT along with IF but have hit a snag. The formula I used was:

IF(SUMPRODUCT(--(--(A1:A10="A")),"A",IF(SUMPRODUCT(--(A1:A10="B")),IF(SUMPRODUCT(--(A1:A10="C")),"A",IF(SUMPRODUCT(--(A1:A10="B")),"B",IF(SUMPRODUCT(--(A1:A10="C")),"C","")))))

Now I know this appears longwinded but until rule 3 it works fine. When trying for rule 4 or 5 the formula only returns FALSE

I'm all for the above formula being tweeked so that it works or for another formula entirely but I've tried searching everywhere and can't find anything on this (although this is probably down to me not phrasing my question/searches correctly).

Any help would be much appreciated. Thanks in advance!

2
excelexcel-formulaexcel-2010

2 Answers

1
votes

Using your provided example, this will yield desired results:

=INDEX({"","C","B","A"},MATCH(SUMPRODUCT({3,2,1},--(COUNTIF(A1:A10,{"A","B","C"})>0)),{0,1,2,3}))
0
votes

Not the easiest answer, but it works as well:

{=IF(SUM(--($A$1:$A$10="A")+(($A$1:$A$10<>"A")*(SUM(--($A$1:$A$10="B"))>0)*(SUM(--(A1:A10="C"))>0)))>0,"A",IF(SUM(--($A$1:$A$10="B"))>0,"B",IF(SUM(--($A$1:$A$10="C"))>0,"C",IF(SUM(--(ISBLANK($A$1:$A$10)))=ROWS($A$1:$A$10),""))))}

Admittedly not nearly as elegant as the above solution.