Subtotals (At least I think that's what I need)

O2Girl

New Member
Joined
Mar 27, 2013
Messages
2
I am using Excel 2011 for Mac.

I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.

In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.

The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.

What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.

I need help figuring this out, please.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
02Girl,

Welcome to MrExcel.

Perhaps something like this to create your category sub totals.....


Excel 2007
ABCDE
1Category£CategorySub Total
2Food1Food5
3Sundries2Drink7
4Food2Sundries4
5Drink3
6Drink1
7Food2
8Drink3
9Sundries2
Sheet3
Cell Formulas
RangeFormula
E2=SUMIF($A$2:$A$20,D2,$B$2:$B$20)
E3=SUMIF($A$2:$A$20,D3,$B$2:$B$20)
E4=SUMIF($A$2:$A$20,D4,$B$2:$B$20)


You can put the formulas wherever suits and then pick up those cells to gather overall totals.
Edit the formula to suit your data range.

Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top