Google-sheets – Sum of all cells in Google Spreadsheet where matching row fits a value

google sheetsworksheet-function

I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value.

For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob" appears in ColumnD, I would expect that the sum of six values from ColumnC would be added together.

My suspicion is the above requires use of a loop, which Google Spreadsheets does not provide.

Best Answer

=SUMIF(D:D,"bob",C:C)

in C column are numbers, in D column are ("bob" or others)

if you wish do not sum all column you can use for example

=SUMIF(D2:D6,"bob",C2:C6)

Note: Text is case sensitive (Bob is not equal bob)

Related Solutions

Google-sheets – Returning the SUM of a range of cells from user-input START to END

Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing.

Take a range of cells as follows

Col  A   B       C
2    1   300     2
3    2   450     4
4    3   700    
5    4   900    
6    5   1000   
7    6   1200

Take these formulas:

Cell A10: 
=sumif(A2:A7,"<="&C2,B2:B7)

Cell A11:
=sumif(A2:A7,">="&C3,B2:B7)

Cell B10:
=sum(B2:B7)

Cell A12:
=(A10+A11-B10)*-1

Cell A12 will have the results you want. Hopefully you get the idea.

Basically you sum all the data elements you don't want in your range, subtract that from the total sum of the range and the remainder is the sum of the range you want.

I didn't do much bug checking for corner cases, but hopefully this will give you enough of a start to solve the problem?

Google Sheets – How to Use an IF Statement Over Several Cells

This is best done like this:

Formula

=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A")
=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A Label SUM(B)'Units'")

Explained

If you include headers, then the QUERY function will add them as such and alter them accordingly, ie. SUM title or COUNT title....etc. You can also added a custom label to the calculated columns, see 2^nd example (3^rd sheet). The rest of the function will perform as standard SQL.

Example

I've created an example file for you: sum over several cells