SUMPRODUCTIF - a inventory spreadsheet

Question

In Google Docs (which shares many commands with excell and other spreadsheet programs) there are functions:

SUMIF: takes a predicate, anda range, and adds up all items in the predicate that meet thepredicate.
SUMPRODUCT: takes 2 column ranges, on each row multiples the element by the element next to it in ther other range, then adds all these up.

+-----+-----------------+------+------------------+
| qty |     product     | item | item per product |
+-----+-----------------+------+------------------+
|   4 | box of foo      | foo  |               10 |
|   8 | foo             | foo  |                1 |
|   2 | box of bar      | bar  |               10 |
|   1 | foorbar package | set  |                1 |
+-----+-----------------+------+------------------+

So I have an order list (basically inventory), of products. Each product contains one or more items. Most of the time is is just of a single typ of item. Except the foobar package which contains 1 foo and one bar.

I want to generate a table summerizing how many of each item we are ordering. which would like:

+------+----------------+
| Item |    Net Qty     |
+------+----------------+
| foo  | 49 (=4*10+8+1) |
| bar  | 29(=2*10+1)    |
+------+----------------+

The stuff in brackets is not part of the output table, just htereto make it clear where each number comes from.

How can I generate such a table? It seems like it could be acomplished by using If, Sum and Product. Using Ifs to make it count things that don't have the name of matching either the item I'm countering of the keyword "set", as zero, and then SUMPRODUCTING the result. But i[m not sure how to accomplish this in one line

I'm not very cluey about If, or doing complex things in spreadsheets in general

This is a one off thing so i don't want to go to the effort of creating a database, then exporting a speadsheet.

This seems relates: http://stackoverflow.com/questions/9128987/sumifs-function-in-google-spreadsheet?rq=1 but it is pretty unclear to me — Frames Catherine White, Jun 15 '12 at 01:28
@Oxinabox, "spreedsheet" is not a word; the existing tags already cover the topic, there is no need for a *new* tag here. — Charles, Jun 15 '12 at 03:19
spreadsheet? I didn't think I created a new tag (might have by mistake) — Frames Catherine White, Jun 15 '12 at 03:20
possible duplicate of [Is there any Excel bulit-in function for SUMPRODUCT IF?](http://stackoverflow.com/questions/10770418/is-there-any-excel-bulit-in-function-for-sumproduct-if) — chris neilsen, Jun 16 '12 at 10:31

score 2 · Accepted Answer · answered Jun 15 '12 at 01:38

2

inspired by this blog, this works for your data:

=arrayformula(sumproduct(
 (($C$2:$C$5="foo")+($C$2:$C$5="set")), $A$2:$A$5, $D$2:$D$5))

answered Jun 15 '12 at 01:38

Aprillion

21,510
5
55
89

SUMPRODUCTIF - a inventory spreadsheet

1 Answers1