CODEWITHSUNDEEP
google-sheets
Kimberley Greig
Kimberley Greig

Reputation: 13

Google Sheets Combine a column with duplicates and update total sum in another colum

This might be something fairly simple but struggling to find a way to do it. In Column B, I have a list of foods required. In Column C, I have the amount needed. In Column D, I have g (for grams) ml (for mills) etc.

I would like to combine the duplicates in Column B and update the totals from Column C, with the g or ml in Column D beside it.

The list I have has been created by using an array formula based on dropdowns in another sheet.

I have seen people using UNIQUE formula in 1 column (this works) and then a SUMIF formula in another column and then a JOIN formula in another... I tried this but the SUMIF is always returning 0.

Would someone please be able to advise on how I can do this?

TIA :D

Upvotes: 1

Views: 739

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

It's hard to be sure exactly what you need without seeing the data. But based on my understanding of solely what you've posted, this QUERY formula should generate a condensed mini-report:

=QUERY({B2:D},"Select Col1, SUM(Col2), Col3 WHERE Col1 Is Not Null GROUP BY Col1, Col3 LABEL SUM(Col2) ''")

In plain English, this means "Arrange the data from the range B2:D in the same order as the raw data, but sum the second column's data according to matches in both the first and third columns. Only return results for the raw data where the first column is not blank. Replace the default 'sum' header on the second column with nothing; I don't need it."

This formula assumes that every ingredient will always be attached to the same measurement (e.g., 'salt' in Col B is always paired with 'mg' in Col D, etc.). If this is not the case, you will wind up with ingredients being listed as many times as there are different measures in Col D.

Upvotes: 2

Related Questions