9

I'm looking for a clean(er) way (than using SUMIF with a third row) to calculate the sum of the results from, say, a VLOOKUP that checks both the first and the second row for matching criterion.

So, given the Transaction spreadsheet, I'd like to generate the data in the Report spreadsheet - ignoring for the moment how I determine which client/account combinations are relevant in the Report spreadsheet - by adding up the values in Transactions which have the given client and account.

Transactions

CLIENT         | ACCOUNT        | VALUE
---------------|----------------|----------------
001            | 001            |  25.00
001            | 001            |   5.00
001            | 002            |  10.00
002            | 002            |  23.00
002            | 002            |   6.00
003            | 001            |   5.00
003            | 001            |   1.25
003            | 001            | 204.00
003            | 003            |  14.00

Report

CLIENT         | ACCOUNT        | TOTAL
---------------|----------------|----------------
001            | 001            |  30.00
001            | 002            |  10.00
002            | 002            |  29.00
003            | 001            | 210.25
003            | 003            |  14.00
pnuts
  • 58,317
  • 11
  • 87
  • 139
Tim Metcalfe
  • 187
  • 1
  • 2
  • 10

2 Answers2

25

The entire report can be generated using the QUERY function:

=QUERY(Transactions!A:C,"select A, B, sum(C) group by A, B label sum(C) 'TOTAL'",0)

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • 5
    Wait, I can do QUERYs? Well that just made life infinitely easier, many thanks. – Tim Metcalfe Jan 27 '14 at 16:38
  • 1
    This works great - thanks. Any ideas how I would go about this if my column B (account) was the date and I wanted to group by the month (eg if 01/02/2014) I want to group everything in 02 (Feb). Thanks – ejntaylor Aug 03 '14 at 10:14
  • @raison you could create a new column called month where you extract the month from the date with `=month(B15)`. Then group by the month column. – Paul Rougieux Aug 02 '16 at 09:15
7

Since this appears to have been viewed rather frequently over the last two years, I thought I'd chime in with an updated answer.

SUMIFS

SUMIFS is my go-to for things like this now. It's SUMIF with multiple possible criteria. So, for something like this, one would use the following function in Report!C1 (assuming Report!1:1 is the first row of data):

=SUMIFS(Transactions!$C:$C,Transactions!$A:$A,$A1,Transactions!$B:$B,$B1)
Tim Metcalfe
  • 187
  • 1
  • 2
  • 10