Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP and sum
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 -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
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. "jeremy via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
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 Wrote: 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 -- Message posted via http://www.officekb.com -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=377122 |
#4
|
|||
|
|||
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?.... -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
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" wrote: 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?.... -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
The data for each month is in a different spreadsheet...
-- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
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.... -- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
You can read more about =sumif() in Excel's help or by visiting Debra
Dalgleish's site: http://www.contextures.com/xlFunctions01.html#SumIf "jeremy via OfficeKB.com" wrote: 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.... -- Message posted via http://www.officekb.com -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|