excel formula?

young Ed

Veteran
come on i know we have a quite a few techies on here!
anyway how do i do a formula for the sum of all cells in a certain column that are also in a row with a certain word/phrase in it if that makes sense?

for example i am setting up a ride log in excel and i have a column for which bike i used as i have 2 different bikes
and i want a total for the number of miles covered on each bike so if miles for that ride are in column B starting in cell B2 and ending in cell B202 and the bike data is in column C starting in cell C2 and ending in cell C202 than i want excel to pick out the distance of all rides bike bike scott speedster 2009 and add all those distances together to give me a total distance covered by that bike and then a total distance in another cell for rides with bike as falcon trailfinder

i could label the bikes as A and B if that helps so the scott could be bike A and the falcon could be B if it would help excel?
Cheers Ed

Supersuperleeds

Legendary Member
Use SUMIF formula to sum the mileage for individual bikes

Supersuperleeds

Legendary Member
List bikes in column A, list mileage in column B

bike name in f3 and put this formula in column h3 =SUMIF(A:A,F3,B : B )

DON'T HAVE ANY GAPS BETWEEN B : B ) - I PUT THE GAPS IN OTHERWISE A SMILEY APPEARS

you can then copy this down into h4 and put the name of the second bike in f4.

You can then cut and move these to where ever you want in the spreadsheet

Venod

Eh up
I may be missing something here, but why not head one Column Scott ie cell B1 and all rides under that will be Scott rides, then do the same for another column for the Falcon.

vernon

Harder than Ronnie Pickering
While you are at it Young Ed, you could also create an input form that will populate your spreadsheet and no, I won't help you with your school ICT project.

swansonj

Guru
Much easier in Access!

Firestorm

Veteran
Or use a pivot table and just refresh the data table whenever you want to see the results

OP

young Ed

Veteran
List bikes in column A, list mileage in column B

bike name in f3 and put this formula in column h3 =SUMIF(A:A,F3,B : B )

DON'T HAVE ANY GAPS BETWEEN B : B ) - I PUT THE GAPS IN OTHERWISE A SMILEY APPEARS

you can then copy this down into h4 and put the name of the second bike in f4.

You can then cut and move these to where ever you want in the spreadsheet
awesome, thanks
with a bit of googling and your post managed to do what i want with the =SUMIF formula

I may be missing something here, but why not head one Column Scott ie cell B1 and all rides under that will be Scott rides, then do the same for another column for the Falcon.
could do, but i'm lazy and i want it all in one big table rather than 2 separate ones
While you are at it Young Ed, you could also create an input form that will populate your spreadsheet and no, I won't help you with your school ICT project.
confused on the input form populate spread sheet thingy! :P anyway sorted it now
you couldn't help me with my school ict project even if you wanted as i don't do ict at school any more so now i just ask on here about everything they did(n't) teach us in school at least 7 years of ICT lessons!
Much easier in Access!
don't understand that, TBH not even sure what it does! haha
Or use a pivot table and just refresh the data table whenever you want to see the results
confused! done now anyway so all good
Cheers Ed

BigAl68

Über Member
I second use a pivot table. Then you can look at all the results in a table or filter on what you want to see.

OP

young Ed

Veteran
@Firestorm and @BigAl68
what exactly is a pivot table? sorry to sound thick!
Cheers Ed

OP

young Ed

Veteran
now i've sorted separate total mileages for each bike
so now that i have a decent working excel spreadsheet with capability for date, distance, bike and notes for each ride and a overall total for all logged rides and separate totals for each bike and a percent completed of your target and miles left to your target (all of these could easily be changed to KM)
anyone want a copy?
Cheers Ed

BigAl68

Über Member
A table you can pivot. That means the columns and data can be dragged and dropped into different places graphically and will summarise the data in various ways i.e. Sum, count average etc. It is a dodle to use. Highlight the data, then click on the insert toolbar and select pivot table. Many easy demos on Google.

Booyaa

Veteran
Or use a pivot table and just refresh the data table whenever you want to see the results
+1 thats what I was going to suggest.