Help with Excel formulas

[pmidwest]

I am setting up a spread sheet to track mileage on 2 company vehicles I have columns like this
Date From To Mileage Category
under each the info for the last year then I have a summary table off to the side
Summary
Category Miles
Commute 3064.1
Business 3064.1
Total 6128.2

In the cell next to Commute I have a simple formula to calculate the mileage and in the total I have a formula to ad commute and business.

My problem is that under the category column in the main table business and commute are mixed up there will be a business then a commute and a few businesses and another few commutes. What I need to know is, is there a way that I can filter that list through my formula. So that the commute formula only adds commutes and the business, business. I don’t know if this makes much since but I'm hoping that someone out there can help.

Thanks in advance

Paul.

 

[ETID]

The pivot table will sum your mileage by catagory...

If you have never used a pivot table, it's easy once you spend a few minutes on it.

 

[pmidwest]

Never used a pivot table, could you give me a little info? point me in the right direction?

Paul

 

[MadForIt]

Hi,

You could try using the SUMIF formula.

Eg.

=SUMIF(A:A,"Business",B:B)
=SUMIF(A:A,"Commute",B:B)

Where A:A is the range where your Categories are stored and B:B is the range where your Milages are stored.

Regards

 

[ETID]

from the menu...

Data>Pivot table and pivot chart> follow the wizard from there

in the select range area, you might want to select the entire column(s), rather than just the range, but for ease let it end the range at your last record for your practice.

In the "Layout" section drag your catagory to the "Row" area
and your mileage to the "data" area....it will defualt to "count" ...dbl click the radio button once it's in the data area and select sum....then select new worksheet and finish.

 

[pmidwest]

Thanks

Paul

 

[DaleWatson123321]

Paul,

You mention having data for the entire past year.

Does this possibly mean that you'd like to be able to generate a Summary by Miles per Category - by time period, example by MONTH or QUARTER ???

I have a file that I can easily modify to accommodate your application.

If you're interested, email me your file, and I'll make the modifications and return it.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

Thanks Dale,

But as of now we only need the total miles per year. I will mention it thought and if anything comes up I'll let you know

Thanks again

Paul