CODEWITHSUNDEEP
excelexcel-formula
Pr0no
Pr0no

Reputation: 4099

How to add values from sheet 1 and present total amount on sheet 2?

Consider the following two sheets in the same Excel workbook:

Expenses

    A       B
1 report  amount
2 0402    10.00
3 0402    15.00
4 0402     5.00
5 1156    40.00
6 1156    30.00

Payouts

    A       B
1 report  total
2 0402    30.00
3 1156    70.00

I'm looking for the formula for cells Payouts!B2 and further down. As can be seen, these cells should just add up the expenses from sheet 1. I have tried for Payouts!B2

=VLOOKUP(A2, 'Expenses'!A1:B6, 'Expenses!'2, FALSE);

But this keeps returning the "there is an error" popup without any further information. What am I doing wrong? Any help is greatly appreciated :-)

Upvotes: 2

Views: 2147

Answers (2)

Werner
Werner

Reputation: 15085

You can also use a pivot table for this:

enter image description here

The pivot table above covers the data set in the range A1:C6 with row labels given by report and values given by the sum of amount. I've also changed the number format to include 2 decimals.

Upvotes: 0

Jerry
Jerry

Reputation: 71568

Okay, since you prefer a formula, the formula that would do the sum you need is =SUMIF().

=SUMIF(Expenses!A1:A6, A2, Expenses!B1:B6)

Upvotes: 4

Related Questions