How to calculate the sum of VLOOKUP with two criteria?

24.6k views Asked by At

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
2

There are 2 answers

3
AdamL On BEST ANSWER

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)

0
Tim Metcalfe On

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)