Categorize Income and Expenses?

[Guest]

I have a checking account register, columns A, B, C, where DEP = deposit,
numbers are check numbers, and positive and negative amounts as shown below.

A B C

CHK# Category Amount

Dep Festival $30.05
1023 Paint -$14.00
1024 Chalk -$33.00
1025 Paint -$12.00
Dep Member $100.00
Dep Festival $44.00

I would like a report in another worksheet or just a reformat of the data
into 2 main categories of Income and Expenses and within each to sum (add)
each category . For example,

Income
Festival $77.05
Member $100.00


Expenses
Paint $26.00
Chalk $33.00


How can I do that?

Thank you,

 

[Guest]

Add column D with formulae along the lines of:

=if(a3="Dep","Income","Expense")

and a heading of "Type" (or any heading other than "CHK#","Category", or
"Amount"). Then select the entire range, including the row with headings and
execute a Data/Pivot Table command sequence.

It seems likely that you haven't used pivot tables before, so you may want
to read up on them before doing this. They'll be very helpful for you.

Best of luck.

Jim

 

[Guest]

Thank you, that worked pretty well. I have some beginning PivotTable
knowledge, so I did that part, what I didn't know was the intermediate step
of creating the new column to get the INCOME/EXPENSEd separated. The only
thing is that Expenses shows first alphabetically in the PivotTable and then
Income. Is there anyway to move Income to the top?

Thank you

 

[Guest]

Right-click on the item that reflects both ("Type" in my example, I think),
then click on "Field Settings", then "Advanced". Select "Descending" under
"Autosort Options" then click "OK" a couple of times.

Glad that you already knew about pivot tables, and glad I could help.

Jim

 

[Guest]

Thank you, just perfect!

Right-click on the item that reflects both ("Type" in my example, I think),
then click on "Field Settings", then "Advanced". Select "Descending" under
"Autosort Options" then click "OK" a couple of times.

Glad that you already knew about pivot tables, and glad I could help.

Jim