2 sheet running total calculations.

[westgross]

I'm using Excel 2003 on Windows XP Sp2. My project has 1 workbook with 2 sheets. The first sheet is a running total of my daily bank activity with the following column headings.

DATE AMOUNT CATEGORY CK# DESCRIPTION

Each row in this sheet has a category name assigned to it. The second sheet, called budget, has the following column headings.

CATEGORY BUDGET AMOUNT ACTUAL AMOUNT DIFFERENCE

Each row in this sheet has a category name corresponding to the bank activity sheet for example.

Wages/Bonuses
Other Income
Mortgage or Rent
Electricity
Groceries
Gas/Oil
Entertainment
Miscellaneous Expense

How do I automatically take the row data "amount" with assigned category name from the bank activity sheet and sum the amount into the budget sheet with the corresponding category name rows under the amount column?

 

[Fazza]

Hi,

Maybe easiest is to use the SUMIF function - help refers.

Also DSUM might be good.

Suggest you include a check sum somehow. Such as, check that the sum of all of the results equals the sum of all of the source data. It would be easy to have a category in the source data and not in the summary sheet and its total could be omitted.

FYI, other ways include via a pivot table. Pivot tables can be great for consolidating lots of data. Such as have a pivot table from the sheet with the daily activity and then populate where you want the results with GETPIVOTDATA functions. Or using a query table instead of a pivot table. If you get into VBA there are other ways again.

HTH, Fazza

 

[westgross]

Thanks but still have questions

SUMIF seems to rely on a fixed range. Since my data is appended to the activity worksheet daily and the rows are assigned category names in the order of the transaction date (in other words, one row may be assigned category name "entertainment", the next row "utilities", so on). Hopefully this makes sense. Maybe I'm not getting it but I am trying to understand the usage from Excel help. If you could give me an example of the formulas you suggested it would spell things out better for me. Thank you!

 

[Fazza]

Hi,

I was away a day, hence my delay in replying. I'm surprised noone else has answered in the meantime.

SUMIF can be across the whole column. So in the budget worksheet's "AMOUNT" column you can have a formula such as

=SUMIF('bank activity'!C:C,A2,'bank activity'!B:B)

Again, there are more sophisticated approaches that offer some advantages so, if you're interested, I suggest you learn a bit more about other approaches.

HTH, Fazza

 

[westgross]

Thanks! A little trial and error with 3d SUMIF.

Thanks for the help. After a little trial and error I got the result I was looking for with the following function that works:

=SUMIF(Checking_Activity!CATEGORY,"=Wages_Bonuses",Checking_Activity!AMOUNT)

 

[westgross]

Macro for function data

Now that I have the following function working,

=SUMIF(Checking_Activity!CATEGORY,"=Wages_Bonuses",Checking_Activity!AMOUNT)

, I was wondering if I can create a macro to prompt me for

CATEGORY,"=name"

within the function?