CODEWITHSUNDEEP
excelvba
Kay
Kay

Reputation: 70

Dynamically calculated the sum of a filtered column on another sheet in excel

I have two sheets in Excel.

On one sheet, I have a table containing a date, a description and a number value.

For example:

1/11/2014 Groceries 23.54
1/12/2014 Dining    36.99
1/18/2014 Groceries 45.55
1/20/2014 Movies    12.99
1/20/2014 Dining    40.24

In my second sheet, I want to have a row that dynamically calculates the sum of all the values with the a specific description.

Groceries : 69.09
Dining : 77.23
Movies : 12.99

Is there a way to do this without VBA?

Upvotes: 0

Views: 4162

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try to use following formula (suppose that your data (Groceries,Dining,..) in sheet1 are in B1:B50 range, their number values in column C, and in sheet2 your data (Groceries, Dining,..) starts from A1):

=SUMIF(Sheet1!$B$1:$B$50,Sheet2!A1,Sheet1!$C$1:$C$50)

just write it in sheet2 in B1 cell and stretch it down

Upvotes: 3

PiotrK
PiotrK

Reputation: 1552

SUMIF is what you're after. It takes 3 arguments:

  • range of cells where you keep your descriptions,
  • the specific description you want the values to be added up,
  • range of cells with the actual numeric values.

To access another sheet's data, use SheetName! before cell's/range's address.

Upvotes: 2

Related Questions