2

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?

Improve this question
1
  • 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.
    – Blindspots ♦
    Commented Apr 4 at 18:20

2 Answers 2

Reset to default
2

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))
Improve this answer
1
  • Thankyou, "The God of Biscuits". This does provide the original solution.
    – timindaburgh
    Commented Apr 3 at 13:49
1

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))})))
Improve this answer
5
  • 1
    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.
    – timindaburgh
    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.
    – Blindspots ♦
    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.
    – timindaburgh
    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 ,
    – Blindspots ♦
    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.
    – Blindspots ♦
    Commented Apr 3 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.