# I want to sum a column in table A when a criteria is met in table B (per row)

Table A:

Exp Inc Cat
\$10 Car Repair
\$300 Salary
\$5 Car Repair
\$300 Salary

Table B:

Cat Sum
Car Repair \$15
Salary \$600

I have been searching query, sumif and other commands to generate Table B from Table A, but I'm not getting anywhere without some very long and complicated equations. Can anyone offer a simple solution?

• I rolled back your edit to the question because requirements shouldn't be changed if you have already received answers based on the original requirements. The recommended approach in this case is to ask a new question which should be self-contained but can also link back to this question for context. Commented Apr 4 at 18:20

Assuming your source table is in columns A:C of your sheet, you can use this formula anywhere to the side of those columns to generate the requested result. Ensure that the source table dollar amounts are numbers formatted as currency, and format the sum column of the destination table similarly.

``````=arrayformula(query({A:A+B:B,C:C},"select Col2,sum(Col1) where Col2 is not null group by Col2 label sum(Col1) 'Sum'",1))
``````
• Thankyou, "The God of Biscuits". This does provide the original solution. Commented Apr 3 at 13:49

The the-god-of-biscuits' approach is a good one. This is merely an alternative.

### BYROW aporoach

``````=BYROW(UNIQUE(TOCOL(C2:C,1)), LAMBDA(r,
{r, SUM(FILTER(A2:B, C2:C=r))}))
``````

### Multiple Sheets

Combining data from multiple sheets.

``````=LET(ranges,{Sheet1!A2:C; Sheet2!A2:C; Sheet3!A2:C},
array,FILTER(ranges, INDEX(rngs,,3)<>""),
cats,INDEX(array,,3), amts,CHOOSECOLS(array,1,2),
BYROW(UNIQUE(cats), LAMBDA(r,
{r, SUM(FILTER(amts, cats=r))})))
``````
• Thankyou, @Blindspots. This is an elegant solution. Now, I'm considering what happens when I need a matrix of categories where the first column has all categories from several tables and matched sums along the rows to the right of that first category column that are pulled from different tables. Commented Apr 3 at 13:53
• I added an example with data sourced from multiple sheets. The named variables `cats` and `amts` are simply to make the formula easier to read and their formulas could instead be included in the LAMBDA. Commented Apr 3 at 14:54
• Thanks @Blindspots. I'll have to digest that MULTIPLE SHEETS reply - which I like on the surface, but I don't have the skills to implement the LET command yet. Commented Apr 3 at 14:59
• LET is easy: `name,value` pairs ending with a single name or formula as the "thing" that LET should return. eg. `=LET(a,1, b,98+a, a+b)=100` or `=LET(a,1, b,98+a, c,a+b, c)=100`. It's effectively `a=1, b=98+1` etc. but instead of equals signs `=` the pairs are separated with commas `, ` Commented Apr 3 at 15:10
• I assumed you wanted to combine the values. If you want a seperate column for each sheet it would be slightly different. Commented Apr 3 at 15:16