Simple yet I can't quite get it right

[ScottBraunDesign]

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.

 

[Bernard Liengme]

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

 

[Bob Phillips]

=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)

 

[susan]

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

 

[krcowen]

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