Adding random amounts in a check register

If all of your categories are in the same column and the amounts are in another than this is possible as well as the dates as I would imagine you want to do this based on time.

You would either on another sheet or off to the side (Your preference) personally another sheet would be best leaving the data as a data sheet (Renamed as data for clear representation).

=SUMIFS(SumRange,CriteriaRange1,CriteriaValue1,CriteriaRange2,CriteriaValue2...)

The above formula is the format of a SUMIFS. This allows us to sum a range of data based on multiple criterias. Take note that this is not SUMIF <-- No S at the end. SUMIF will allow you to sum a range based on one criteria only... IMO I don't think the single form of this should ever be used as the syntax is flipped, causes confusion for some and really is not needed as the plural version can do a single criteria if the operator would like!

Now your sheet will have the data in columns... Lets pretend column A on the DATA sheet is your Dates column. B Being your Categories and C being your Amounts

You would setup your report tab (Lets call it Report) with a list of categories either as column headers or individual lines... again this is a preference.

For now I am going to assume you list them out in rows....

Lets assume your first category you have listed out is in B5 and you want to show the sum in C5 Right next to it... Above C5 you have January... Only dont type January type in C4 1/1/2016 and then right click on the cell to format it. In this window you can choose CUSTOM at the bottom of the list and type in the Custom window MMMM. This will display the date as Month name but will retain the value of 1/1/2016.

Your formula can now utilize that date to show the totals within that Month.

Please Login or Register  to view this content.

Again -

Data!$C:$C = Amounts
Data!$B:$B = Categories
Data!$A:$A = Dates
C$4 = Date (This is 1/1/2016 in the example... and by using EOMonth() we were able to also use 1/31/2016)
$C5 = Current Category (In the example would equal your "Charity" Category)

Please take a moment to go through this and let me know what you got or have questions on. I know you said you use to do this so let me know if anything needs further clarification or if you got it from her.

Cheers - ELeGault

Sounds good - The drop down is called Data Validation > List

So there are multiple things that can be going on with the old one.. The first is likely the right one based on the formula you have outlined and that is a Table.

Highlight your entire dataset and hit CTRL+T or CTRL+L or go to the INSERT tab up top and hit Table.
You should have your headers selected, so the box for "My data has headers" should be selected.

Next you will go to the design tab which will only be visible when you are inside your table (Any cell selected in the table). You will then check off the option to add a Total row to your table (Should be visible near the center of the ribbon)

Now you can go to the bottom row of the data, here there are drop downs to choose how you would like to total your data... Sum, Avg, Count, etc...

Now your table can be filtered down to a category and the total should reflect only that in which is visible!


Your next option... PIVOT TABLE
This is an effective tool for ripping apart data.

Again, highlight the data (or table if it is now a table) and go to the Insert tab. Here select PIVOT TABLE.
Now you can build out your pivot table to fit your needs. I will not describe a layout as this becomes overly opinionated and there are 6 ways to sunday to get a finalized view.


The last option would be to setup dynamic formulas to recognize an option selected in a drop down list... if you are interested in this option feel free to reach out, but I feel that based on your description of first book it is a TABLE.


If you have difficulties in accomplishing this feel free to reach out again... It is always quickest if we post a file with a Before tab and after tab that may not be functioning but illustrates the goal with a look and feel. Someone in the community can always create/re-create visuals with a sample file. As always make sure your data is scrubbed for any sensitive materials (Especially bank information/account numbers etc...) before posting for your protection!

Good luck (And remember to mark resolved if this ends up being the answer you have been looking for)

Cheers

Only just now got back to this project. I had already formatted my data as a table...but it won't let me check the box for total row. Any idea why? I had applied "Table Style Medium 2" --is there something about that particular style that doesn't allow totals? [EDIT: I took a look at the old worksheet and it's the same table style but it DOES have the total box checked.]

If I have some time in the next few days, I will try to create a dummy file to post.

I appreciate your patience with me!

Another edit

So I did the obvious thing: I made a copy of the old worksheet and then replaced the old data with the new data--and now I've got subtotals! I don't have the satisfaction of knowing how it was done, but it works. At least the quick trial I gave it seems to work... Probably won't get to work on it again till Wednesday to really give it a good look.

Yea it is odd and without seeing the NON working file to test changes I cannot say for certain what is causing the issue.

If you want, clear sensitive data and post it with the table so that we can offer suggestions.

Cheers