Help with vlookup results for summary

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I gotta vlookup table and on tha other worksheets people enter the info from
dropdown boxes. I need to show the books sold and the average of looks at it
after being chosen to be listed as a summary.

A B C
red book $15 x
Blue book $10 x
red book $20 w

So if a "red book" "$15", a "blue book" "$10" and a "red book" "$20" was
sold, and I want to list red book or W books, how can I display this summary
info?

It should look something like this so i can use the figures for sums and
division.
Red Book | 2 | $35
Blue book | 1 | $10
and/or
Red Book | 1 | $20 w - based on W.

Now need a result.
eg. 2 red books divide 43 people who looked at it gives me the result I need.

That's it!
 
.. how can I display this summary info?

Suggest you try a pivot table (PT).
It's ideal for what you have in mind, and takes only a few moments to set up.

Here's some steps to guide you in ..

First, insert a top col header row, and enter col labels
so that your table looks like this:

Book Sale Code
red book $15 x
Blue book $10 x
red book $20 w
etc

Select any cell within the table,
click Data > Pivot table .. .
Click Next > Next

In step 3 of the wizard, click Layout
Drag n drop Book in ROW area
Drag n drop Book in DATA area
Drag n drop Sale in DATA area
Click OK > Finish

Go to the PT sheet
Drag the field header "Data", drop it over "Total",
to give you the required result,
viz something like what you posted:
Red Book | 2 | $35
Blue book | 1 | $10
Click to expand...

Now, let's do the 2nd pivot which brings in the "Code" as well ..

Again, just select any cell within the source table,
click Data > Pivot table .. .
Click Next > Next

Answer Yes to the prompt (use less memory ...)
Click Next

In step 3 of the wizard, click Layout
Drag n drop Book in ROW area
(Double-click on it, check "None" under Subtotals > OK)

Drag n drop Code in ROW area
Drag n drop Book in DATA area
Drag n drop Sale in DATA area

Click OK > Finish

Go to the new PT sheet
Drag the field header "Data", drop it over "Total",
which'll give you the other required result,
viz it'll look something like this:

Book Code Cnt.Bk Sum.Sale
Blue book x 1 10

red book w 1 20
x 1 15


---
 
Back
Top