I have a list I want to pull totals from but I don't know how to d

[jlsflyer]

--
Ask the right questions you get the right answers.

 

[Pete_UK]

Well, if you describe your problem in more detail then you might get
some answers !!

Have a look at SUMIF and SUMPRODUCT in XL Help.

Hope this helps.

Pete

 

[Don Guillett]

So true

Ask the right questions you get the right answers

 

[jlsflyer]

Well, I had to start the ball somehow, ok detail, I have been told I need to
use some type of macros to do what I want to do and not just a lookup but as
you probably figured out I'm a rookie so I need more than that to go on. I
want to match the date of the expense and get a monthly total seperate for
business and personal, the date of the expense and get a seperate yearly
total for business and personal, the type of expense and get seperate overall
totals for business and personal, the type of expense and get seperate totals
for the type of payment seperated into business and personal.

Here is the list:

Type of Expense Date Id Type of Payment
Amount

Personal>Auto>Gasoline 30-Jan-09 7 Credit Card
$38.25
Business>Office>Supplies 31-Jan-09 9 Credit Card
$25.63
Business>Auto>Repair 31-Jan-09 10 Cash $68.59
Business>Auto>Gasoline 1-Feb-09 11 Check $26.14
Business>Expense>Meal 1-Feb-09 12 Credit Card $29.58
Business>Auto>Repair 24-Jan-09 16 Credit Card
$27.00
Business>Expense>Meal 1-Feb-09 13 Check $89.63
Personal>Auto>Repair 1-Feb-09 14 Debit Card
$325.47
Business>Office>Supplies 1-Feb-09 15 Debit Card $48.93
Business>Auto>Gasoline 29-Jan-09 8 Credit Card
$40.00
Personal>Auto>Gasoline 4-Feb-09 17 Cash
$20.25
Business>Expense>Meal 1-Dec-08 2 Credit Card $32.58
Business>Auto>Repair 24-Dec-08 3 Credit Card
$22.00
Business>Expense>Meal 1-Dec-08 5 Check
$72.63
Personal>Bills>Phone 2-Dec-08 2 Check $105.95

Here is what I need totals for:

Business - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

Personal - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

I didn't exactly know how to phrase this query in the opening question, sorry.

J

 

[jlsflyer]

Sorry if I was vague Don, this should change that.

Ok, detail, I have been told I need to use some type of macros to do what I
want to do and not just a lookup but as you probably figured out I'm a rookie
so I need more than that to go on. I want to match the date of the expense
and get a monthly total seperate for business and personal, the date of the
expense and get a seperate yearly total for business and personal, the type
of expense and get seperate overall totals for business and personal, the
type of expense and get seperate totals for the type of payment seperated
into business and personal.

Here is the list:

Type of Expense Date Id Type of Payment
Amount

Personal>Auto>Gasoline 30-Jan-09 7 Credit Card
$38.25
Business>Office>Supplies 31-Jan-09 9 Credit Card
$25.63
Business>Auto>Repair 31-Jan-09 10 Cash $68.59
Business>Auto>Gasoline 1-Feb-09 11 Check $26.14
Business>Expense>Meal 1-Feb-09 12 Credit Card $29.58
Business>Auto>Repair 24-Jan-09 16 Credit Card
$27.00
Business>Expense>Meal 1-Feb-09 13 Check $89.63
Personal>Auto>Repair 1-Feb-09 14 Debit Card
$325.47
Business>Office>Supplies 1-Feb-09 15 Debit Card $48.93
Business>Auto>Gasoline 29-Jan-09 8 Credit Card
$40.00
Personal>Auto>Gasoline 4-Feb-09 17 Cash
$20.25
Business>Expense>Meal 1-Dec-08 2 Credit Card $32.58
Business>Auto>Repair 24-Dec-08 3 Credit Card
$22.00
Business>Expense>Meal 1-Dec-08 5 Check
$72.63
Personal>Bills>Phone 2-Dec-08 2 Check $105.95

Here is what I need totals for:

Business - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

Personal - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

Sorry, I didn't exactly know how to phrase this query in the opening
question but I would appreciate any help you can give me.

J

 

[jlsflyer]

As I feared the list got screwed up in the post, here it is again:

Type of Expense Date Id Type of Payment
Amount

Personal>Auto>Gasoline 30-Jan-09 7 Credit Card $38.25
Business>Office>Supplies 31-Jan-09 9 Credit Card $25.63
Business>Auto>Repair 31-Jan-09 10 Cash
$68.59
Business>Auto>Gasoline 1-Feb-09 11 Check $26.14
Business>Expense>Meal 1-Feb-09 12 Credit Card $29.58
Business>Auto>Repair 24-Jan-09 16 Credit Card $27.00
Business>Expense>Meal 1-Feb-09 13 Check $89.63
Personal>Auto>Repair 1-Feb-09 14 Debit Card $325.47
Business>Office>Supplies 1-Feb-09 15 Debit Card $48.93
Business>Auto>Gasoline 29-Jan-09 8 Credit Card $40.00
Personal>Auto>Gasoline 4-Feb-09 17 Cash
$20.25
Business>Expense>Meal 1-Dec-08 2 Credit Card $32.58
Business>Auto>Repair 24-Dec-08 3 Credit Card
$22.00
Business>Expense>Meal 1-Dec-08 5 Check $72.63
Personal>Bills>Phone 2-Dec-08 2 Check $105.95

Here is what I need totals for:

Business - Monthly Yearly Credit-Card Debit-Card Cash Type of Expense

Personal - Monthly Yearly Credit-Card Debit-Card Cash Type of Expense

Hopefully it comes through intact this time, if not, the sequence is:

Type of Expense/Date/Id/Type of Payment/Amount

 

[Ron Rosenfeld]

Sorry if I was vague Don, this should change that.

Ok, detail, I have been told I need to use some type of macros to do what I
want to do and not just a lookup but as you probably figured out I'm a rookie
so I need more than that to go on. I want to match the date of the expense
and get a monthly total seperate for business and personal, the date of the
expense and get a seperate yearly total for business and personal, the type
of expense and get seperate overall totals for business and personal, the
type of expense and get seperate totals for the type of payment seperated
into business and personal.

Here is the list:

Type of Expense Date Id Type of Payment
Amount

Personal>Auto>Gasoline 30-Jan-09 7 Credit Card
$38.25
Business>Office>Supplies 31-Jan-09 9 Credit Card
$25.63
Business>Auto>Repair 31-Jan-09 10 Cash $68.59
Business>Auto>Gasoline 1-Feb-09 11 Check $26.14
Business>Expense>Meal 1-Feb-09 12 Credit Card $29.58
Business>Auto>Repair 24-Jan-09 16 Credit Card
$27.00
Business>Expense>Meal 1-Feb-09 13 Check $89.63
Personal>Auto>Repair 1-Feb-09 14 Debit Card
$325.47
Business>Office>Supplies 1-Feb-09 15 Debit Card $48.93
Business>Auto>Gasoline 29-Jan-09 8 Credit Card
$40.00
Personal>Auto>Gasoline 4-Feb-09 17 Cash
$20.25
Business>Expense>Meal 1-Dec-08 2 Credit Card $32.58
Business>Auto>Repair 24-Dec-08 3 Credit Card
$22.00
Business>Expense>Meal 1-Dec-08 5 Check
$72.63
Personal>Bills>Phone 2-Dec-08 2 Check $105.95

Here is what I need totals for:

Business - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

Personal - Monthly Yearly Credit Card Debit Card Cash Type
of Expense

Sorry, I didn't exactly know how to phrase this query in the opening
question but I would appreciate any help you can give me.

J

You might try looking at Pivot Tables.

Here's something I put together quickly based on what you've written, but there
are numerous possible variations.


Amounts Dates
2008 2009 Grand Total
Dec Jan Feb
Bus/Personal
Business $127.21 $161.22 $194.28 $482.71
Personal $105.95 $38.25 $345.72 $489.92
Grand Total $233.16 $199.47 $540.00 $972.63

If you expand any (or all) of the months, you can get a breakdown by type of
payment. I didn't do it as the word wrap would make the results unreadable.

Here's a partial sample just showing the Dec 2008 charges:

Amounts Dates
2008
Dec Dec Total
Bus/Personal Check Credit Card
Business $72.63 $54.58 $127.21
Personal $105.95 $105.95
Grand Total $178.58 $54.58 $233.16




To create this, I
dragged Type of Expense to Row Labels
Amounts to Values

Then I selected the Business types and "grouped" them
Then I selected the Personal types and "grouped" them

The columns I "grouped" by months and years
Then I "expanded" the columns to show Type of Payment.
--ron

 

[jlsflyer]

Thanks Ron, I need to run out and do some things right now but I'm sure I'll
have some questions for you once I disect your response and compare it to my
experience level. I'll get back to you later on. Thanks again.

Jon

 

[jlsflyer]

Ron, I'm sorry I haven't responded to you sooner, I had a death in the family
and I haven't had time for anything but finalizing those affairs. I had a
chance to review your answer to my question and I'm trying to digest how you
got to the results you are showing. Can you send me the details on how you
produced those results as far as fomulas or functions. It would be very
helpful. And I hope you will be patient with me I'm pretty new to Excel.
Thank you.

Jon

 

[jlsflyer]

Ok, I figured out how to work the pivot table, excellent recommendation.
Now...is there any way to have the pivot table update automatically when I
input new numbers into the list or do I have to go back and pull up a new
pivot table and specify additional cells every time? Thanks Ron.

Jon

 

[Ron Rosenfeld]

Ok, I figured out how to work the pivot table, excellent recommendation.
Now...is there any way to have the pivot table update automatically when I
input new numbers into the list or do I have to go back and pull up a new
pivot table and specify additional cells every time? Thanks Ron.

Jon

At least in Excel 2007, there is a change data source option which would allow
you to include new rows without doing a new pivot table each time. This could
be automated via a macro, if necessary.
--ron

 

[jlsflyer]

I'm using excel 2007, can you give me an idea of how to activate that change
data source option and honestly Ron, I've got no clue what a macro is or
does. I'll go read about it now but any help would be much appreciated.
Thanks.

j

 

[Ron Rosenfeld]

I'm using excel 2007, can you give me an idea of how to activate that change
data source option and honestly Ron, I've got no clue what a macro is or
does. I'll go read about it now but any help would be much appreciated.
Thanks.

Assuming you have the default ribbon setup:

Select some cell within your pivot table.

Select "Pivot Tools" (That will be at the very top of the Excel window, a bit
to the right of center).

Select "Options"

In the Data ribbon, there will be two buttons: Refresh; and Change Data
Source.

--ron

 

[Bernd P]

Hello Jon,

Ron gave you already a solution with Pivot tables.

Here is another one with VBA:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd

 

[jlsflyer]

That's pretty sharp stuff Bernd, how did you create the helper column? Also,
how did you seperate the "type of expense" column into TOE, Cat and Sub Cat
columns.
That is a very nice layout. Also, Ron told me how to refresh or change data
on the pivot table but it would be more efficient for me if the table or
Sfreq would update automatically once the data was added to the list. Is
that possible to do? Thank you very much for the input.

 

[Bernd P]

Hello Jon,

The helper column:
=Text(D3,"YYYY-MM")
and copy down.

Personal>Auto>Gasoline you can split into 3 cells by selecting three
horizontal cells and array-entering
=wssplit(A1,">")

WSSplit you will find here:
http://www.sulprobil.com/html/wssplit.html

If you set your worksheet to calculation = automatic then your cells
values will update instantaneously if input cells change.

Have fun,
Bernd

 

[jlsflyer]

Bernd, you are the man! Thanks, I'll get to it and see how it works. Thanks.

j

 

[Bernd P]

Hello Jon,

You are welcome.

Please keep in mind that my code is still quite fresh. Test it and let
me know if there are problems.

Regards,
Bernd

 

[jlsflyer]

I'm actually having a little trouble getting it to work, my own ignorance at
this point. I'll try to work through it more tomorrow. It would be nice if
you could email the actual workbook so I could see how you have the functions
arranged. I'll spend more time on it tomorrow and let you know how it goes.
Thanks Bernd.

j

 

[Irene]

Dear Friend ,

Thanks for Your Reply to my Mail/Proposal.

My Client Wishes to Invest her Financial Estate worth $60Million in Your
Country or Abroad under Your Direct Supervision, Your Assistance is therefore
Needed in Receiving, Investing and Managing the $60Million in any viable and
lucrative area of buisiness.

You will be Paid 10% of the Total Amount, and an Additional 10% from the
ROI. I will demand Your 100% Honesty, Confidentiality and Cooperation of
these Matter. As my Clients Position as a Former Government Official does not
Permit Her to Own or Operate an Over Sea Account or Company, as soon as You
Assure me You can Work with my Terms then we can Proceed.

I will want You to give Details on how You plan to Invest the Money, and
what should be Expected on returns whether in Your Country or Over Sea.I
shall be needing the information below to enable me carry out all relevant
paper works that will back the claim of Funds.

Do Confirm Your Interest by providing the Information below:

Your Full Name
Company Name
Complete Address
Direct Phone
Age/Occupation

I will wait to hear from You, and also provide all above Information.

Barnabas Irane
E-mail (e-mail address removed)
Phone: +229 96-333-443