Excel Formula help.

General cycling advice ( NOT technical ! )
User avatar
Redvee
Posts: 2336
Joined: 8 Mar 2010, 8:58pm

Excel Formula help.

Postby Redvee » 27 Aug 2014, 1:03am

I've got an Excel spreadsheet with my mileage along with a few other details. One of the columns is which bike I rode. What I want to do is total up the mileage for each bike.

Column K has the bike noted, A, B, C etc and column C has the mileage for that particular ride.
What I want to do is have a cell at the bottom of the month sheet with the total mileage for Bike A, B & C

User avatar
NUKe
Posts: 3992
Joined: 23 Apr 2007, 11:07pm
Location: Suffolk

Re: Excel Formula help.

Postby NUKe » 27 Aug 2014, 1:43am

Simplest would be to use. SUMIF just look in help for the syntax.
NUKe
_____________________________________

tim-b
Posts: 1674
Joined: 10 Oct 2009, 8:20am

Re: Excel Formula help.

Postby tim-b » 27 Aug 2014, 6:21am

Hi

Mine look like this with a separate tab for each bike, but otherwise sounds the same as yours

The function (SUM) that I used is shown in the formula bar

You seem to have a single sheet for each month. If you want a total mileage for all three bikes, as opposed to each of your bikes, then you could run SUM on each column total

Regards
tim-b
Image Attachments
XLS.jpg
~~~~¯\(ツ)/¯~~~~

User avatar
barrym
Posts: 633
Joined: 22 Jun 2012, 10:05am
Location: Corsham - North Wilts

Re: Excel Formula help.

Postby barrym » 27 Aug 2014, 6:37am

Take a look at pivot tables. They take a bit of work to understand to start but is worth the effort.

Barry
--
Cheers
Barry

tatanab
Posts: 4344
Joined: 8 Feb 2007, 12:37pm

Re: Excel Formula help.

Postby tatanab » 27 Aug 2014, 6:54am

Taken from a similar exercise but nothing to do with cycling

=SUMIF(C2:C62,"Red",E2:E62)

If Red appears in the range C2 to C62 (could be red bike) then add up the appropriate cells in the range E2 to E62

Ayesha
Posts: 4192
Joined: 30 Jan 2010, 9:54am

Re: Excel Formula help.

Postby Ayesha » 27 Aug 2014, 7:05am

When you have the individual mileages for several bikes for consecutive years, you could draw a ‘Waterfall’ chart displaying the decrements and gains of mileage for each bike.

skicat
Posts: 517
Joined: 21 Jun 2011, 1:09pm
Location: NCN52 / SL8

Re: Excel Formula help.

Postby skicat » 27 Aug 2014, 9:02am

What you want is (assuming there are 100 rows in the spreadsheet. Change yours to suit)

Code: Select all

Total for bike 'A':
     =SUMIF($K1:$K100,"A",$C1:$C100)
Total for bike 'B':
     =SUMIF($K1:$K100,"B",$C1:$C100)
Total for bike 'C':
     =SUMIF($K1:$K100,"C",$C1:$C100)
The hurrier I go, the behinder I get

Psamathe
Posts: 12063
Joined: 10 Jan 2014, 8:56pm

Re: Excel Formula help.

Postby Psamathe » 27 Aug 2014, 10:06am

I use
=FIXED(SUMIF($N$8:$N$1000,$I$2, $B$8:$B$1000),0,TRUE)&" Miles"


(which gives me a text 999 Miles)

Ian

User avatar
Redvee
Posts: 2336
Joined: 8 Mar 2010, 8:58pm

Re: Excel Formula help.

Postby Redvee » 27 Aug 2014, 10:49am

I went with NUKe's suggestion of SUMIF, a quick Google last night got me the formula and I just entered my own criteria to suit.

Cheers all.

User avatar
[XAP]Bob
Posts: 17972
Joined: 26 Sep 2008, 4:12pm

Re: Excel Formula help.

Postby [XAP]Bob » 27 Aug 2014, 10:21pm

Do have a look at pivot tables, then break the date up - year, month, dow.

Then you can make some interesting tables ;)
A shortcut has to be a challenge, otherwise it would just be the way. No situation is so dire that panic cannot make it worse.
There are two kinds of people in this world: those can extrapolate from incomplete data.