How to sum a range based on another range which comes under another range in sheets

Question

I wanted to know how can we sum a range in a sheet based on another range which comes under another range.

Example: I want the sum of A2:A10 if corresponding cell B1:B10 value comes under a list which is D2:D or E2:E

in Example,

Sum of Fruits:    ? 
Sum of Vegetable: ?

Sample Sheet Structure

Welcome. Please don't provide sample data in a snapshot. Anyone trying to recreate the problem is put off straightaway because there's no simple way to recreate the data. — Tedinoz, Feb 06 '19 at 09:21
Use `DSUM`. Vegetables: `=dsum(A1:B8,"Count",E1:E3)`, Fruit: `=dsum(A1:B8,"count",D1:D6)`. Change the value of cell B1, D1 and E1 to "exactly" the same word. FWIW, I used "Type" but the actual word doesn't matter so long as the value in the first row of the labels in the 'database' is the same as the value in the first row of the 'criteria'. Read the [documentation](https://support.google.com/docs/answer/3094281). — Tedinoz, Feb 06 '19 at 09:40

score 0 · Accepted Answer · answered Feb 06 '19 at 18:51

0

=DSUM({"x", "y"; A2:B}, "x", {"y"; FILTER(E2:E, E2:E<>"")})

=DSUM({"x", "y"; A2:B}, "x", {"y"; FILTER(D2:D, D2:D<>"")})

EU syntax:

answered Feb 06 '19 at 18:51

player0

This uses DSUM with two arrays and a FILTER, which sounds pretty ingenious (and doesn’t require the change in header of my approach). But, would you break down the formula and explain the logic of each section? – Tedinoz Feb 07 '19 at 12:05
@Tedinoz sure. basically, this works on appending a virtual row on range A2:B and E2:E just so DSUM would work without changing the actual headers. "x" and "y" are variables. DSUM does the sum based on some criteria. `{"x", "y"; A2:B}` will append two virtual cells with content "x" and "y" next to each other on top of the range A2:B. 2nd "x" is for DSUM to point where is the column for summing. `FILTER(E2:E, E2:E<>"")` will filter range E2:E and eliminate all empty rows. (not using filter would result in incorrect sum). and again "y" is virtually appended over this filtered range E2:E – player0 Feb 07 '19 at 16:01
@player0 Thank you. I will have to let that sink in ;). I do think your answer would be improved were you to edit it to include those comments. On a tangent, would you care to cast your eye over (https://stackoverflow.com/questions/54541923). – Tedinoz Feb 07 '19 at 23:43
@Tedinoz I had a look, left two answers and still no idea what OP is after there xD – player0 Feb 08 '19 at 01:10
@player0 I could see what he was getting at and your first answer was spot on - one minor edit (worth fixing in the answer), but spot on. Thanks. – Tedinoz Feb 09 '19 at 00:35

1 Answers1