Google-sheets – Sum using two filter variables

formulasgoogle sheets

So I have a ledger sheet that was set up to record costs for a single project. We've added a project column so we can track more than one project so every entry (row) now is identified per project. I'm already running some SUMIF functions to total a column if another column matches a criteria, but I now need to add in another criterion…

For example:

My existing formula sums column J where column E = "sometext"

=Sumif(Ledger!$E$7:$E$900, "Amberon", Ledger!$J$7:$J$900)

How can I expand this to sum only when two columns match?

My newly added project identifier is column Ledger!$A$7:$A$900

Ideally, I'll only have one set of sum values but will select which project they apply to based on the value in a cell with a picker (List of projects).

I've read about SUMIFS but can't seem to understand the syntax.

Best Answer

=SUMIFS(A1:A17, B1:B17, "Dragon",
                C1:C17, "Kate")

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

the PIVOT C transposes the unique results from column C (turning rows into columns)
the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
the WHERE B IS NOT NULL ignores empty cells
the SUM(B) adds the result of the query
the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
the SUM function will add them together.
the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).

Google-sheets – Filter by the sum of two cells

Use the following formula to accomplish that.

I used the following dataset: enter image description here

Take notice of the extra ID I added.

Formula

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

Explained

The two WHERE clauses will set the criteria:

Column C to 2500
The result of the summation of column B + E to be larger or equal than 20

Column F and D are there to categorise the result.

Result

enter image description here

Example

I've created an example file for you: Filter by the sum of two cells in Google Spreadsheet

Best Answer

Related Solutions

Google-sheets – How Do I Create a Google Spreadsheet Formula to SUM data based on two variables

Formula

Explained

Screenshot

Example

Note

Google-sheets – Filter by the sum of two cells

Formula

Explained

Result

Example

Related Topic