Google Sheets – A Function Opposed to SUMIF

This is best done like this:

Formula

1. =QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A")
2. =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

References

1. SQL
2. QUERY formula
3. Query Language Reference
4. Question with same techniques used

I'd suggest a pivot table and for this it may be a good idea to label your Original Table (say Fruit in A4 and Qty in B4) though not essential. Then select from A4 to as far down in B as you are likely to require (but can be changed later if still insufficient) and Data, Pivot table report..., click on - Add field next to Rows and select Fruit, click on - Add field next to Values and select Qty.

You can copy the resulting table back in to the original sheet if you wish.