Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Let's say we have some data in a spreadsheet like:

  A         B          C
Price    Quantity    Genre
 20        2500       Car   
 10        1000       Car
 10        2500       Bike

I can filter Cars with Quantity 2500 with FILTER(A:A;B:B=2500;C:C="Car") - easy.

But what if there is another column, for postage, like:

  A         B          C         D
Price    Quantity    Genre    Postage
 20        2500       Car       0
 10        1000       Car       6
 10        2500       Bike      10

I want to filter to find everything who's total price (postage+price) is 20, and I have 2500 of.

I tried various combinations but can't seem to do it. Should I create a sum column first?

share|improve this question

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.