To suppress the header, end the query string with label sum(A) ''
.
The blank spot below the header comes up because the column B, besides the letters a,b,c, also contains a bunch of blank cells, which form a group of their own. Since you don't want them, add the clause where B<>''
.
End result:
=query(A1:B, "select sum(A) where B<>'' group by B label sum(A) ''")
(Case doesn't matter: I prefer lowercase because more text fits into the same horizontal space.)
I slept on it, and the solution miraculously came to me! Here's how I've gotten it working, in case anyone else is attempting something similar.
First off I am counting the number of times a value appears in Column A (the names column, in my example).
COUNTIF($A$1:$B,$A1)
This will return a value of 1 for Patrick and a value of 2 for both Mike and Sarah.
I am using a FILTER() function to create a searchable range which only includes rows which have the same Column A value as the row I'm currently formatting.
FILTER($A$1:$B,EQ($A$1:$A,$A1))
I am then using this as the range for a second COUNTIF() function, which will count how many times the value in Column B appears.
COUNTIF(FILTER($A$1:$B,EQ($A$1:$A,$A1)),$B1)
This will return a 1 for Patrick and a 2 for both of Mike's rows, the same as the value from the previous COUNTIF(). However, both of Sarah's rows will return a 1, since "12" and "11" both appear only once beside her name.
If all appearances of the Column A value have the same Column B value, then these numbers should match. If there are multiple unique Column B values, the second number will be smaller. So all that remains is to compare the two and return TRUE if my first number is greater than the second.
Full formula:
=COUNTIF($A$1:$B,$A1)>COUNTIF(FILTER($A$1:$B,EQ($A$1:$A,$A1)),$B1)
This will return FALSE for Patrick and Mike, but TRUE for Sarah's rows, correctly flagging them as containing an error.
Best Answer