How to use SUMPRODUCT on all rows that contain a string?

Question

I have two spreadsheets, one named "Expenses" and one named "Supplies".

The Expenses sheet contains the following columns: category, item, # per unit, quantity, cost. This sheet contains a log of all purchased items and contains duplicate values in the "item" column.

The Supplies sheet contains the following columns: category, item, in stock. This sheet only contains unique values in the "item" column.

I am trying to calculate the value of the "in stock" column on the Supplies sheet by summing together the product of "# per unit" and "quantity" for each instance that an item is listed.

For example, if I have the following table in the Expenses sheet:

yarn, red, 2, 3, $5

yarn, blue, 1, 4, $2

buttons, black, 2, 12, $3

yarn, blue, 3, 3, $18

Then the resulting Supplies sheet should looks like this:

yarn, red, 6

yarn, blue, 13

buttons, black, 24

I've managed to get partway there. I've written this function: =SUMPRODUCT(INDEX('Expenses'!$C$2:$C,MATCH(B2,'Expenses'!$B$2:$B,0)),INDEX('Expenses'!$D$2:$D,MATCH(B2,'Expenses'!$B$2:$B,0))) but it does not take duplicate values into account; it only returns the product for the first row that matches the item name criteria in the Expenses sheet.

How can I alter my formula to work for ALL rows in Expenses that match the item name?

To help you get a more accurate answer, it would be beneficial if you could provide a minimal, reproducible example, such as a dummy sheet, that demonstrates the issue you're facing. This would allow the community to better understand your problem and provide targeted solutions. If you're unsure, please refer to Stack Overflow Guide on Creating a Minimal, Reproducible Example. Including a reproducible example will greatly increase your chances of receiving a helpful response. — SputnikDrunk2
– SputnikDrunk2, Commented May 23, 2023 at 0:47
It seems you only matching color. You also need to match product as well. Use FILTER() instead of INDEX/MATCH. — Harun24hr
– Harun24hr, Commented May 23, 2023 at 2:48

The God of Biscuits · Accepted Answer · 2023-05-23 06:38:06Z

2

I think SUMPRODUCT is the wrong approach here. Instead, use a nested QUERY in your 'Supplies' tab:

=query(query({Expenses!A2:E},
"select Col1,Col2,Col3*Col4 where Col1 is not null",0),
"select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label sum(Col3) ''",0)

N.B I'm guessing at the location of the data in the Expenses tab; adjust as appropriate. The inner QUERY calculates the product of # per unit & quantity per row; the outer then sums these products by each unique category/item pair. It's also possible to include your headers in the QUERY input array as well and pass them through to the output although for consistency with your original request I've not done that here.

answered May 23, 2023 at 6:38

The God of Biscuits

3,3672 gold badges6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use SUMPRODUCT on all rows that contain a string?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related