Often when I'm QA-ing a spreadsheet at work, I'll create a column that checks some aspects of the inputs or results. That column will have TRUE/FALSE values. Often I end up with hundreds (or thousands) of rows, and I want to check that none of them are FALSE (i.e. some error is present). For one column it's easy enough to just filter for FALSE.
If there were multiple columns, I could just count the number of cells in the range that are FALSE. But this made me think that I could use the inverse method (i.e. count the number of cells that are TRUE) to check if all cells had the same values.
The main idea is to count the number of cells that equal a certain value. In this case since we are wanting to check that all the cells have the same value, you can just check the range against the value of any of the cells within the range. In this case, we can check if count the number of cells that equal our criteria value is equal to the total number of cells.
| A | B | |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
| 6 | TRUE | TRUE |
| 7 | =COUNTIF(A1:A5,A1)=COUNT(A1:A5) | =COUNTIF(B1:B5,B1)=COUNT(B1:B5) |
Now let's try that same formula, but using text values this time.
| A | B | |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | A | |
| 4 | A | A |
| 5 | A | A |
| 6 | FALSE | FALSE |
| 7 | =COUNTIF(A1:A5,A1)=COUNT(A1:A5) | =COUNTIF(B1:B5,B1)=COUNT(B1:B5) |
This doesn't work, because COUNT() returns the number of cells that are numbers. For non-numbers, we have to use COUNTA() which counts the number of non-empty cells. [NOTE: TRUE and FALSE are treated as non-numbers by COUNT(), too]
| A | B | |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | A | |
| 4 | A | A |
| 5 | A | A |
| 6 | TRUE | TRUE |
| 7 | =COUNTIF(A1:A5,A1)=COUNTA(A1:A5) | =COUNTIF(B1:B5,B1)=COUNTA(B1:B5) |
So, now we have a formula that works with both numbers and non-numbers. Sort of...
You may have noted that for the examples in column B that all of the cells are not the same, i.e. row 3 is a blank cell which is not equal to 1 or 3, therefore not all the cells are the same. That isn't necessarily a bad thing as there may be times when we would want to ignore empty cells. But that wasn't what I started out trying to do, i.e. "check if count the number of cells that equal our criteria value is equal to the total number of cells". Another way to state this is to "check that there are no cells that do not equal our criteria value", i.e. the count of cells that don't match the criteria value is equal to zero.
The solution I came up with is to use SUMPRODUCT().
| A | B | |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | A | |
| 4 | A | A |
| 5 | A | A |
| 6 | TRUE | FALSE |
| 7 | =SUMPRODUCT(1*NOT(A1:A5=A1))=0 | =SUMPRODUCT(1*NOT(B1:B5=B1))=0) |
Awesome! Now we have two formulas; one that ignores empty cells and one that looks at all the cells!
Not so fast, neither versions are case-sensitive.
| A | B | |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | a | a |
| 4 | A | A |
| 5 | A | A |
| 6 | TRUE | TRUE |
| 7 | =SUMPRODUCT(1*NOT(A1:A5=A1))=0 | =COUNTIF(B1:B5,B1)=COUNTA(B1:B5) |
Again, there may be times when we want to ignore case. But I still hadn't met my goal of checking where all cells in a range are exactly the same.
To accomplish this we can use EXACT(), which is aptly named for our purposes.
| A | B | |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | A | a |
| 4 | A | A |
| 5 | A | A |
| 6 | TRUE | TRUE |
| 7 | =SUMPRODUCT(1*NOT(EXACT(A1:A5,A1)))=0 | =SUMPRODUCT(1*NOT(EXACT(B1:B5,B1)))=0 |
Now we have a single version that can be used for numbers and text (case-sensitive). If you want to use this and ignore empty cells, just replace the "=0" part with "=COUNTA(A1:A5)".
Even though I started out with a single goal, I ended up figuring out multiple approaches, each with their own potential uses.
"I have not failed, not once. I’ve discovered ten thousand ways that don’t work." - Thomas Edison (on creating the light bulb).