I have a sheet setup to record my gas usage. The sheet is setup like so:

Week | gallons | price | vehicle | gallons | price | vehicle | and so on.
1 3.23 $3.50 Bike 3.45 $3.89 stang .......
2 3.23 $3.50 Bike 3.45 $3.89 bike .......
3 3.23 $3.50 stang 3.45 $3.89 bike .......


I want to look take all of the information at the end of the week and find
out how much i spent on my car and how much i spent on my bike. I want to
have a column on the right side of my sheet that will reference only the
cells that are labled bike and add them up and put the total amount of money
I spent in gas for my bike in that week. and the same for my mustang.

You have made life very difficult for yourselg. HAd the data been set out as
bewlo, a simple filter or Listt would work
Week | gallons | price | vehicle
1 \ 3.23 \ $3.50 \ Bike
1 \ 3.45 \ $3.89 \ stang
.....
Are you prepared fro a VBA solution to the orginal data?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme




"ScottBraunDesign" wrote in
message ...
I have a sheet setup to record my gas usage. The sheet is setup like so:

Week | gallons | price | vehicle | gallons | price | vehicle | and so on.
1 3.23 $3.50 Bike 3.45 $3.89 stang
.......
2 3.23 $3.50 Bike 3.45 $3.89 bike
.......
3 3.23 $3.50 stang 3.45 $3.89 bike
.......


I want to look take all of the information at the end of the week and find
out how much i spent on my car and how much i spent on my bike. I want to
have a column on the right side of my sheet that will reference only the
cells that are labled bike and add them up and put the total amount of
money
I spent in gas for my bike in that week. and the same for my mustang.

=SUMPRODUCT(--($D2:G2="Bike"),$B2:E2,$C2:F2)

Just change the G2,E2,F2 to the last possible cells that you use, BUT NOTE
the offsets and maintain that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ScottBraunDesign" wrote in
message ...
I have a sheet setup to record my gas usage. The sheet is setup like so:

Week | gallons | price | vehicle | gallons | price | vehicle | and so on.
1 3.23 $3.50 Bike 3.45 $3.89 stang
.......
2 3.23 $3.50 Bike 3.45 $3.89 bike
.......
3 3.23 $3.50 stang 3.45 $3.89 bike
.......


I want to look take all of the information at the end of the week and find
out how much i spent on my car and how much i spent on my bike. I want to
have a column on the right side of my sheet that will reference only the
cells that are labled bike and add them up and put the total amount of
money
I spent in gas for my bike in that week. and the same for my mustang.

i am still learning about exct - but wouldn't a pivot table work? if not, why?

"ScottBraunDesign" wrote:

I have a sheet setup to record my gas usage. The sheet is setup like so:

Week | gallons | price | vehicle | gallons | price | vehicle | and so on.
1 3.23 $3.50 Bike 3.45 $3.89 stang .......
2 3.23 $3.50 Bike 3.45 $3.89 bike .......
3 3.23 $3.50 stang 3.45 $3.89 bike .......


I want to look take all of the information at the end of the week and find
out how much i spent on my car and how much i spent on my bike. I want to
have a column on the right side of my sheet that will reference only the
cells that are labled bike and add them up and put the total amount of money
I spent in gas for my bike in that week. and the same for my mustang.

Susan

If Scott had set up his data the way Bernard just recommended, a pivot
table would work fine. As it is, with repeated column headings and
the same type of data (e.g. priece, vehicles, etc.) in multiple
columns he won't be able to get his data with a pivot table or a
filter. It may as well have been typed into a work document, almost.
Bob's sumproduct formula could save him, but, he should probably
rearrange the data so he use some of the advantages that a spreadsheet
provides.

Ken


On May 22, 3:55*pm, susan wrote:
i am still learning about exct - but wouldn't a pivot table work? *if not, why?



"ScottBraunDesign" wrote:
I have a sheet setup to record my gas usage. The sheet is setup like so:

Week | gallons | price | vehicle | gallons | price | vehicle | and so on..
* *1 * * * * *3.23 * *$3.50 * *Bike * * * *3.45 * * $3.89 * * stang * .......
* *2 * * * * *3.23 * *$3.50 * *Bike * * * *3.45 * * $3.89 * * bike * * .......
* *3 * * * * *3.23 * *$3.50 * *stang * * *3.45 * * $3.89 * * bike * * .......

I want to look take all of the information at the end of the week and find
out how much i spent on my car and how much i spent on my bike. I want to
have a column on the right side of my sheet that will reference only the
cells that are labled bike and add them up and put the total amount of money
I spent in gas for my bike in that week. and the same for my mustang.- Hide quoted text -

- Show quoted text -