VLOOKUP and sum

[jeremy via OfficeKB.com]

I have a list of project numbers, their monthly sales, and, 5 categories
the projects fall into. For each month, I need to associate the project
numbers with the five categories, and, sum the monthly sales for each
category. (i.e. be able to paste the row of project numbers and
associated sales into a spreadsheet and be able to produce the monthly
sales of each of the 5 categories
e.g.

project # category

<4000 S
4101 O
4102 C
4103 G
4104 I
4105 G
4106 C
4107 O
4108 O
4109 C


project# sales category (S, O, C, G or I) sum of sales

4106wb $11,664.00 ? sum of S =
4117 $581.81 sum of O =
1026 $41.56 sum of C =
1040 $573.02 sum of G =
SK00 $1,332.90 sum of I =
KR00 ($1,367.22)
WS00 ($319.63)
4093 $24,920.00
1040 $12,319.00
4118 $159,773.75

This is a little beyond my excel knowledge, but this is what I have so far
for associating the project numbers to the assigned category:

=IF(4000<A7<5000, VLOOKUP(A7,A1:B10,2), "S")

This sort of works if there are no letters in project numbers (they have no
meaning for this purpose), and I still need sum the sales….

Any help?

Thanks, Jeremy

 

[Barb R.]

I'd do this with a PIVOT Table.

Let's say you have data in the following form

PROJECT # Category Monthly Sales
<4000 S $5000
4101 O $4000

etc.

Select the cell with all of your data. Ensure that the labels are the top
row of the data selected.

Data -> PIVOT Table
Microsoft Excel list or database
Pivot table report
Select NEXT
Select NEXT again if you've already selected the cells with the data.
For this example, put the pivot table on a NEW WORKSHEET

Select CATEGORY and put it in the area labeled DROP ROW FIELDS HERE.
Select SALES and put it in the area labeled DROP DATA ITEMS HERE.
Click on the cell that reads "Count of Sales". Right click and pull down to
FIELD SETTINGS.
Select SUMMARIZE by SUM and click on OK.

If you have other questions, please come back.

 

[Morrigan]

It sounds like you already have a column that indicates the category of
the project. Can you just do a SUMIF(TargetRange,Category,SumRange)?

 

[jeremy via OfficeKB.com]

Thanks...
I have about 60 months of data. I was hoping to set up spreadsheet
template, so all i would have to do is copy/paste the project# and sales
columns from each month into the template spreadsheet....

Does that makes sense?....

 

[Barb R.]

Do you have the MONTH in a separate column? If so, include that in the
PIVOT TABLE SOURCE and put the MONTH field in the DROP COLUMN FIELDS HERE
location. Click on the arrow next to MONTH and select only the months you
want to use.

 

[jeremy via OfficeKB.com]

The data for each month is in a different spreadsheet...

 

[jeremy via OfficeKB.com]

Morrigan

Can you explain a little--I'm a newer user....

I know the category of each project number (5 categories). Each month we
have sales for several projects. I need to sum up the sales for each
category (industrial, oil/gas, etc) for the month. So i'll have 5 sums for
each month and one total sales sum....

Clear?

SUMIF -- how does this work....

 

[Dave Peterson]

You can read more about =sumif() in Excel's help or by visiting Debra
Dalgleish's site:

http://www.contextures.com/xlFunctions01.html#SumIf