Google Sheets - formula to sum per category

Question

In this answer I learned how to sum a column of amounts, converting them to a specific currency as well.

As a continuation to this question - each row has a "category" column, and these category values repeat themselves. In a new sheet I listed all category names (so each name appears only once), and I need to get the sum above split into these categories.

The following sample has "Sheet1" with general data (and formula), and "Sheet2" has data split into unique category names - I need help with this sheet to get the formula from the first sheet to work in this sheet.

(The sample has only a few rows, the real data has thousands of rows and categories, so I cannot just do individual formulas)

Answer 1

Try this formula in Cell A1 of Sheet2:

=query({
 Sheet1!A2:A,
 MAP(Sheet1!B2:B,Sheet1!C2:C,LAMBDA(val,cur,IFERROR(val*GOOGLEFINANCE("CURRENCY:"&cur&"USD"),val)))
 },
 "select Col1, sum(Col2) where Col1 is not null group by Col1 
   label Col1 'category', sum(Col2) 'Sum'")

---

LOGIC

• MAP(Sheet1!B2:B,Sheet1!C2:C,LAMBDA(val,cur,IFERROR(val*GOOGLEFINANCE("CURRENCY:"&cur&"USD"),val)))

  • returns the USD value of each currency value in each row.

• query({Sheet1!A2:A,...

  • a query is used to sum and group the results
  • Column 1 is the category value
  • Column 2 is the USD converted value

• select Col1, sum(Col2) where Col1 is not null group by Col1

  • Sum the USD value and group by the category

• label Col1 'category', sum(Col2) 'Sum'

  • Housekeeping for header labels

---

RESULTS

---