Groups

    ...

    ...

    10 Comments

    Filter the data, click on the column header with descriptions in it > tools > filter > auto filter > choose petrol from drop down box.

    put this in a cell up the top so it will show the total even when filtered, you will obviously have to change ranges to suit your data. (make sure the 9 stays the same though)

    =SUBTOTAL(9,A4:A6)

    pivot table function maybe your best bet....
    highlight the data...then click data > pivot table >next >layout> and then play around with it

    basic way to do it is:-

    click on the columns which contains the petrol prices and the word petrol,
    at the top of the screen click on the icon sort ascending or descending
    it will sort stuff into alphabetical order and their corresponding price

    hope that helps

    Couple of options, you can sort the data by column so it lists the petrol ones altogether and then subtotal those only, or if there are not to many entries you can click on a cell you want the answer to be in and then do =a3+a9+a22 etc with the a3 a9 a22 etc being the cell that relate to petrol figure - hope this makes sense

    bod emrys;7937520

    Thanks everyone!I tried Greg_68's suggestion first and quite like that … Thanks everyone!I tried Greg_68's suggestion first and quite like that way of doing it, then I tried the pivot table which Abz suggested and am amazed with how easily I can sort through the data. I'm going to play about with it a bit more, but it looks like I'm sorted.Rep to all of you for your input :thumbsup:



    No probs :thumbsup:

    Pivot table is an amazing little tool and its great for analysing large volumes of data

    Abz;7937581

    No probs :thumbsup:Pivot table is an amazing little tool and its great … No probs :thumbsup:Pivot table is an amazing little tool and its great for analysing large volumes of data



    Dead right they are absolutley fantastic, I can do in 2 minutes what people take 3-4 hours at work. When I show them some of my macros they want them all too.

    Pivot tables can be a bit daunting to beginners though and there are few that can be bothered to try and sort them out, but once they do they don't look back.

    OP glad you are sorted.

    Why not simply use a 'sumif' ?

    This gives you in my opinion for that purpose more flexibility than a pivot.

    The formula would work like this: put this into B1:
    =SUMIF($D$1:$D$441,A1,$C$19:$C$441)

    in A1 you fould have the description, for example 'petrol'
    in column 'C' you have the amounts spent
    in column 'D' you have the description next to each item, so petrol, insurance, ...

    Hope that helps

    It looks for a match anywhere within D1:D441 for whatever you have typed in A1 and then adds up the corresponding figure in C1:C441.

    So if your descriptors are in D1:D441 and your costs against them in C1:C441 you put this formula in B1. When you type the descriptor in A1 it will put the total against it in B1.

    HTH

    Thanks Greg for explaining this more clearly. Kind of hard to explain excel formulas in words I find
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Rest In Peace Jerry Lewis.66
      2. So, whose already pre-ordered a Scorpio and where from?66
      3. Are these two things distinctly different to you?99
      4. ❅☁☁❅ I want☼to talk☼about the☔WEATHER☔no politics☃no religion❅☁☁❅18846240

      See more discussions