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,

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



"gee14" wrote:

> 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,
>
>
>

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

"Jim Skrydlak" wrote:

> 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
>
>
>
> "gee14" wrote:
>
> > 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,
> >
> >
> >

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

"gee14" wrote:

> 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
>
> "Jim Skrydlak" wrote:
>
> > 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
> >
> >
> >
> > "gee14" wrote:
> >
> > > 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,
> > >
> > >
> > >

Thank you, just perfect!

"Jim Skrydlak" wrote:

> 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
>
> "gee14" wrote:
>
> > 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
> >
> > "Jim Skrydlak" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "gee14" wrote:
> > >
> > > > 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,
> > > >
> > > >
> > > >