Google-sheets – Google Spreadsheet sum when filtering

google sheets

I have spreadsheet that looks like this:

Now when I filter on a company I want the total to display the sum of the whole of B column.

Since I am very new to spreadsheets I was hoping someone could help me on how I would do that?

=SUBTOTAL(9, B2:B20)

Formula

=QUERY(DATA!C1:F7, "SELECT C, MAX(E), MAX(F) GROUP BY C PIVOT D")

enter image description here

In order to get the summation, I simply added a summation for the consecutive columns (see example).

I've created an example file for you: SUM and MAX in PIVOT TABLE

Use the following formula to accomplish that.

I used the following dataset: enter image description here

Take notice of the extra ID I added.

=QUERY(DATA!B1:F4, "SELECT F, D WHERE B+E>=20 AND C=2500")

The two WHERE clauses will set the criteria:

Column F and D are there to categorise the result.

enter image description here