Is there a shorthand way to compare a range of cells to a single value?

0
votes

I would like to write a formula that returns "True" if ALL of a specific set of cells are marked with an 'X'. For example below....

  A B C D E
1 X
2
3 X X
4   X
5     X

One way to check that ALL of those values have an 'X' is with the formula below

=IF( AND(A1 = "X", A3 = "X", B3 = "X", B4 = "X", C5 = "X"), "True", "false")

However, A3:B3 is a similar grouping, but the comparison statement doesn't recognize that for the purposes

=IF( AND(A1 = "X", A3:B3 = "X", B4 = "X", C5 = "X"), "True", "false")

Is there a shorthand way to compare a range of cells to a single value?

4
excelexcel-formula
not that I am aware of. If the X's were line up in a row or column or filled all cells of 2D range, you could do something along the line of COUNTIF(A1:A5,"X")=5. That would return TRUE only if all cells in A1:A5 had an X in them. – Forward Ed
looks like you could do something like =COUNTIF(A1:C5,"X")=COUNTA(A1:C5) – BigBen
You can compare a range to a single value in multiple functions if you are in array mode, and in a few specific functions otherwise. What are you exactly trying to do? – Jerry

4 Answers

1
votes

While you cannot use 'braces' (e.g. { and } to construct a discontiguous array of cell references (e.g. {A1, A3:B3, B4, C5} you can use the braces to construct an array of string literals (e.g. {"A1", "A3:B3", "B4", "C5"} and INDIRECT uses string literals to define ranges.

=SUM(COUNTIF(INDIRECT({"A1","A3:B3","B4","C5"}),"X"))

The COUNTIF produces an array of results from the array of string literals being interpreted by INDIRECT. The SUM takes a total of that array of results.

That produces an accurate COUNTIF. If you want TRUE/FALSE then compare it to the result if they were all X.

'any contiguous part of the discontiguous union can be grouped
=SUM(COUNTIF(INDIRECT({"A1","A3","B3","B4","C5"}),"X"))=5
=SUM(COUNTIF(INDIRECT({"A1","A3:B3","B4","C5"}),"X"))=5
=SUM(COUNTIF(INDIRECT({"A1","A3","B3:B4","C5"}),"X"))=5

Those are not array formulas.

1
votes

You could also set up a template in a second sheet for the cells you want to contain an X then just compare them:

=SUMPRODUCT((Sheet1!A1:C5=Sheet2!A1:C5)*(Sheet1!A1:C5="X"))

or to get a true/false result:

=SUMPRODUCT((Sheet1!A1:C5=Sheet2!A1:C5)*(Sheet1!A1:C5="X"))=COUNTA(Sheet2!A1:C5)

Sheet2 (template)

enter image description here

Sheet 1 (to be compared)

enter image description here

0
votes

Use Control-Shift-Enter when entering the second one to use arrays like single values.

=IF( AND(A1 = "X", A3:B3 = "X", B4 = "X", C5 = "X"), "True", "false") <ctrl-shift-enter>
0
votes

In cell F1 Type:

=IF(COUNTA(A1:E1)>=5, "True", "False")

In the example you provided, it should return false for all of them. It will return true if all cells have an X in them.