Google-sheets – How to SUMIFS text beginning with apostrophe

With the following formula you can accomplish that.

Formula

=QUERY(DATA!D2:F15, "SELECT D, SUM(F) GROUP BY D PIVOT E LABEL D 'Politicians'")

Explained

The QUERY function will group the summation of the costs per politician and pivot the outcome per type.

Screenshot

Example

I've prepared an example for you: overview with query and pivot

When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.

Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.