1. ## VLOOKUP (?) (2000)

I have a spreadsheet with several columns of data. Column B represents an amount of a commodity I purchase on a given day (column A is the date, with the possibility of multiple purchases on the same date). Column C represents the location where I purchase the commodity, and column D the price. I would like to summarize on one line (say at the top of the spreadsheet before any purchases are listed) the total of purchases at each location for the month (i. e., the locations in column C), and the average price of the purchases for that month.
Any help?

2. ## Re: VLOOKUP (?) (2000)

Add a column that gives the month
=month(date)
for example in E4:
=Month(A4)

Now use the pivot table wizard to make a table.
row = location
column = month
data = sum of amount and average of price

Steve

3. ## Re: VLOOKUP (?) (2000)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> jlkirk

Would you like to use a Pivot Table for this project, or subtotaling, or maybe some VBA to really do multiple things all at once?

I would prefer the Pivot Table, so check it out in the On-Line Help, and if you need any help let us know.

Post an example, with factious data no doubt; workbook and we'll try to get you a starting point.

Wassim

4. ## Re: VLOOKUP (?) (2000)

Steve,
I have never used pivot tables before. What say I put together a smaller version of my worksheet, and let you walk me through it-ok?
Thanks,
Jeff

5. ## Re: VLOOKUP (?) (2000)

Steve,
Here is a sample month, with a section at the top of the worksheet where I would like to have the results appear.
Jeff

6. ## Re: VLOOKUP (?) (2000)

Jeff,

If filling in the yellow section at the top is all you want, it is relatively easy; you don't need a pivot table for that.

To calculate the total volumes per location in C3:C6, you can use a SUMIF formula. For example, the formula in cell C3 would be

=SUMIF(\$G\$11:\$G\$50,"o",\$P\$11:\$P\$50)

G11:G50 is the range containing the locations to test on; "o" is the code for the Onshore location, and P11:P50 is the range to sum. Formulas for C4, C5 and C6 are similar, with "r", "t" and "c" instead of "o".

To calculate the total cost per location in D36, you can also use a SUMIF formula. In D3:

=SUMIF(\$G\$11:\$G\$50,"o",\$V\$11:\$V\$50)

and analogously in D4, D5 and D6.

Finally, the average cost is (if I understand correctly) total cost/total volume, so the formula in E3 would be

=D3/C3

and analogously in E4, E5 and E6

7. ## Re: VLOOKUP (?) (2000)

Thanks, Hans-right on the money!

8. ## Re: VLOOKUP (?) (2000)

Hans,
One last question-suppose I want to multiply the volumes by a weighting that woul be in another column. How would I accomplish that?
Thanks,
Jeff

9. ## Re: VLOOKUP (?) (2000)

There are several possible solutions. For example: place the product of volume and weight factor in a column, and use that column as the last argument in the SUMIF function. You can hide this column if you don't want users to see the intermediate results.

10. ## Re: VLOOKUP (?) (2000)

Thanks, Hans. I got a little creative and got my results via an array formula.

11. ## Re: VLOOKUP (?) (2000)

Good for you. That was one of the other options I had in mind. Should work just as well.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•