... - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# ...

6y, 9m agoPosted 6 years, 9 months ago
...
6y, 9m agoPosted 6 years, 9 months ago
Options

(10)
1 Like #1
try this?

dunno if it will help?
1 Like #2
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)
1 Like #3
pivot table function maybe your best bet....
highlight the data...then click data > pivot table >next >layout> and then play around with it
1 Like #4
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
1 Like #5
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
#6
bod emrys
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
#7
Abz
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.

1 Like #8
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 :)
#9
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
#10
Thanks Greg for explaining this more clearly. Kind of hard to explain excel formulas in words I find :)