Ā You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hello again, so I have this spreadsheet which details visitor numbers by hourly slots for the last three years.
Each hour has a separate row.
Is there a formula to pull the =SUM of all say 13:00 hour slots, 14:00 time slots etc etc?
I basically want to show the footfall for the year by hour to show busiest hour etc.
Cheers guys!
[img] 
[/img]
Probably easiest in a pivot table.
edit: although I have had my Excel knowledge shown up before... šÆ
^This^
SUMIF will do it once you've got your timecodes sorted
Have a mars bar if you get it right first time š
you need SUMIF
=SUMIF( range, "13:00") for exanple
edit: 11 seconds too late
=SUMIF(D80:D100,"13:00:00",F80:F100)
If you want a formula some creative SUMIF would probably do it.
I suspect pivot tables are the easiest(?) way to do it...
And of course - Excel is not the best tool for the job!
The ratio of "Movements" to "Visitors" is quite high? Is this tracking the Picolax visitors centre?
I would add a column with a IF formula referencin the time and then sum the column. This gives you an easy visual check everything is workiƱg
Pretty much said, SUMIF will do it but it's the ugly way.
Pivot Table all day š
Cool thanks, I will have a play with this mysterious 'pivot table' thing.....
not sure why I keep getting asked to do excel work... š
Pivot table for sure
People think its a dark art its not and you will become the excel master.
Pivot table for sure
People think its a dark art its not and you will become the excel master.
this is a simple pivot table and chart with your data. will give you an idea. They are great for summarising long lists of repeated data.
[url= https://drive.google.com/file/d/0ByzV4BaEka8_c0otOGpJYTVKVDQ/view?usp=sharing ]Pivot Table[/url]
the key with pivots is to get the source data in the same format and in named columns. in your spreadsheet remove the first row as that will create problems making the pivot table.
as others have said, make sure the data formatting is correct for all the cells or you'll get weird results when you summarise the data.
SUMIF will do it once you've got your timecodes sortedHave a mars bar if you get it right first time
So no mars bar for me š
What do you mean sort out your time codes?
Disagree about the first row.
Just don't select it.
oh my life....that was way easier than pages of formula!
[img] 
[/img]
Disagree about the first row.Just don't select it.
Fair point, but if OP doesn't know anything about pivot tables then the first thing that will happen when he selects the data sheet is that it will fail because the first row doesn't have column headings in it.
Its all goid success was achieved!
4am is the perfect break in time.
Whats the address?
If you right click in the table you can make an interactive graph.
Create pivot chart or something like that.