INDEX and sum

I have a table of products in rows against years in columns with revenues in 
the data array.  I want to sum revenues for a 'x' year period starting at 'y' 
year for product 'p' on a separate worksheet. So it's a 2D lookup.

Following useful tips from the community I was able to find the revenue for 
'y' year for product 'p', using INDEX: 
=INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product 
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0))
- where $C14 gives the product name and $Q$2 shows the year to look up.

I was even able to find the decrement figures 3 rows down using offset:
=OFFSET(INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product 
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0)),3,0)

but I couldn't make the summation to variable numbers of years across.

I had thought that I might be able to use the ADDRESS function and then sum 
the range, but I couldn't find a way that would work.

Any suggestions ?  I couldn't see this question asked previously ...



0
Utf
2/8/2010 10:40:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
753 Views

Similar Articles

[PageSpeed] 10

Hi,
If I didn't misunderstood you have in C14 the product name and in Q2 the 
year and your information is in the sheet called New Products sales where in 
cell A you have the product name, in cell B you have the year and you want to 
sum column P

=sumproduct(--(C14=New Product Sales!$A$1:$A$1000),--($Q$2=New Product 
Sales!$B$1:$B$1000),New Product Sales!$P$1:$P$1000)

change range to fit your needs

If this helps please click yes thanks

"doctorbarry1947" wrote:

> I have a table of products in rows against years in columns with revenues in 
> the data array.  I want to sum revenues for a 'x' year period starting at 'y' 
> year for product 'p' on a separate worksheet. So it's a 2D lookup.
> 
> Following useful tips from the community I was able to find the revenue for 
> 'y' year for product 'p', using INDEX: 
> =INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product 
> sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0))
> - where $C14 gives the product name and $Q$2 shows the year to look up.
> 
> I was even able to find the decrement figures 3 rows down using offset:
> =OFFSET(INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product 
> sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0)),3,0)
> 
> but I couldn't make the summation to variable numbers of years across.
> 
> I had thought that I might be able to use the ADDRESS function and then sum 
> the range, but I couldn't find a way that would work.
> 
> Any suggestions ?  I couldn't see this question asked previously ...
> 
> 
> 
0
Utf
2/8/2010 1:19:01 PM
Reply:

Similar Artilces:

how to deduct a % from sum #2
How would I change this formula so that I can deduct 5% of the calculation? The 5% figure would be put in a seperate cell so that the % could be altered, meaning 5% would be in say cell A2, and I might require to change the 5% to say 7%. The % figure relates to an inflation level to be deducted from a lump sum of money. =D9+D10-D11 My attempt... =D9+D10-D11-(A2%) In your case, depending on how you stored the percentage: =(D9+D10-D11)*(1-A2) But take a look at my standard reply about percentages in excel: ============================================================= About percen...

Sum based on three variables
Hello Group I have created a database of labels we send to outside fillers. What I would like to do (without using autofilter or pivot tables) is sum the number of labels sent to a filler by month. So for example range a1:a200 = date with a short date in range b1:b200 (mmm - I thought it would be easier to calculate), range c1:c200 = filler (we have five), range d1:d200 = label description and finally range e1:e200 is the quantity of labels sent. So a running total could be maintained by Month, Filler and total of labels sent for the month. I do not have permission to access VBA/Macros (A...

using sum button
I try pasting a bunch of numbers from a website into excel. Everything comes over fine, but when I try to sum the column using the sigma button, the result is just zero. Please help! I have a lot to sum. Thanks for your help. Adam sounds like the "numbers" are being pasted as text. That would probably be the case if trailing spaces are included in the copy. Regards Trevor "adam h" <anonymous@discussions.microsoft.com> wrote in message news:0a7601c3baac$b7e2c820$a001280a@phx.gbl... > I try pasting a bunch of numbers from a website into > excel. Ever...

Sum function in report not working properly
I have a report that is separated alphabetically by employee. This report totals the number of hours an employee worked during the entire week and places the sum in the employee footer on the report. The control source is =Sum([On Std Hours]). For one employee his total for the week should have been 34.5 hours but it is showing up as 139.75 hours. Another shows 32 hours but it should have been 25.5 hours. The data that the report is pulling from is correct and there is obviously no error in the control source formula. Why is my report totaling the wrong sum and what can I do to fix it? ...

index & match month
I would like a formula that would match any month I want. IF statements have a max of 7 criteria and I need more than that. I would like to enter a month in another spreadsheet and return the data for that month from the data worksheet. Type Jan Feb Mar Apr May Jun Jul Aug Sep Oct LR 87.55 126.77 111.63 174.66 132.49 117.70 135.82 203.85 184.11 177.43 PC 3.91 1.90 6.63 15.33 23.54 126.09 116.21 RE 98.23 29.75 17.27 5.52 10.25 - SP 92.93 - Lets say you input type into cell Z1, ...

Total greater than sum
HI Guys, Got a storage issue taht lies probably in the fundamentals of exchange databases. My Priv1.ebd is 9GB and the corresponding stm is 2GB. Problem is that the sum of my mail boxes sizes only comes to around 3 GB. Where is the difference? Do I need to do a reindex to shrink that database? I was thinking that this came from the dabase with no deleting deleted mails or not being reallocating the space elsewhere. I recall makring not to delete mail from the boxes for 30 days after deletion (for user stuff-ups), but I have no idea where the "pending file" lives nor how to re...

when i change data in a sum forumla, the sum doesn't change?
i am doing a simple sum formula, using it several times in the worksheet, and it used to work fine, however, suddenly now the formulas are only working after the initial calculation, if i go back and change data later on, the formula doesn't reflect any changes and special pasting of the formula only reproduces the value of the original calculation! I want the Sum function to work as it should. Reflect changes as they are made and be able to paste a function to another group of cells! Under Tools->Options->Calculation make sure Automatic is checked "richard_kta"...

Full-Text Indexing #3
Is there a way to enable full-text indexing in an outlook client for Public Folders when the client is working offline? ...

Summing a group of numbers
Awhile back I could hold down the right mouse button and select a group of numbers. The sum of these numbers would show on the bottom of the page. I have lost the ability to do this. Is there a way to get it back? See your other post. "Barbie Da" <BarbieDa@discussions.microsoft.com> wrote in message news:D02A2CF6-85AF-45CC-AE4F-8F3D4610233A@microsoft.com... > Awhile back I could hold down the right mouse button and select a group of > numbers. The sum of these numbers would show on the bottom of the page. > I > have lost the ability to do this. Is ther...

SUM PROBLEM
I have a Query consisting of two table where I have created two additional columns. One column is a normal Price*Qty and the Other one I Discount that column by a number that is filled into the discount field. ( With an Else Iif Statement to keep the Original price if No Discount is Given. In the Query it works perfectly. Both my Columns are set to a Currency property and the Other Fileds are Number fields ( Discount & Qty) When I tru to create a REPORT it refuses to Sum any of these Fields. I have tried the Wizard and it doesn't even give me the Option of Summing certain Fields...

Sum of data with two criteria
Hi there, i have a (simple!?) problem with the following.. In my sheet, i have 3 columns: column A, containing a order-number column B, containing a quota column C, containing a week-number Now what needs to bee counted, is the SUM of the quota (column B) occurences from a specific order, AND a specific week! Problem is, the rows can contain multiple occurences of an ordernumber... I'm feeling quitte stupid, can anyone help me please? :confused: --- Message posted from http://www.ExcelForum.com/ Hi try =SUMPRODUCT((A1:A1000=order_number)*(C1:C1000=week_number),(B1:B1000)) Fr...

Cell formating and summing imperial weights
Hi all, Does anybody know how to format cells for using imperial weight (pounds, ounces and drams) and how do I then summ these values to als give the result in pounds, ounces and drams -- Wood ----------------------------------------------------------------------- Woody's Profile: http://www.msusenet.com/member.php?userid=575 View this thread: http://www.msusenet.com/t-187117693 On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com> wrote: > >Hi all, > >Does anybody know how to format cells for using imperial weights >(pounds, ounces and dram...

Follow up question about consolidating dups and sums
I used the pivot table and it worked like a charm. However, the data is going to be transferred into another program and it needs to be in the same format as the original with headers like: State County Tons Commodity I have played around with the pivot table some, but it is not right. Perhaps a formula or function would work better? Thanks! -- taz0923 This would be really easy to do with MS Access, but challenging in MS Excel. In Excel, you could list all counties and products on a seperate worksheet then use the sumproduct formula to lookup the sums. Not...

Subtracting One Sum From Another
I have a very simple and basic question, but I'm just learning Excel, so please bear with me. The situation as follows: I have a simple spread sheet, columns C, E, and G are input, outgo, and balance (In that order) The first four (4) rows are no problem, simple add C to previous G for Balance. Then when I go to do output it's basically the same, g5 is G4 minus e5 My question: What is the formula I need enter only once for the Balance column? Disregard the first four rows. I'm entering each cell individually =sum(e5-g4) =sum(g5-e6) etc. It really is a bummer entering all t...

Match, Index, Indirect, Offset
I often see these functions used together and used quite a lot but what practical application do they have? From reading the help files it is easy to understand the theory of what's going on but applying it in practice is something else. Sumproduct was a classic case in point here. Going by the help file, we are told that sumproduct multiplies corresponding values in arrays and then sums the result (yeah but so what). But who would have guessed from this that it's application is quite extensive as a filtering tool of a sort. ----== Posted via Newsfeeds.Com - Unlimited-Unrest...

how do I add rows on excell using the =sum function
i have 100 200 300 about 7 numbers and I would like to know how to add them. =sum (g1-15)? If your numbers are in the range G1 to G15 try it like this: =SUM(G1:G15) When you need to reference a range of cells think of the colon as the word "to". G1 to G15 = G1:G15. -- Biff Microsoft Excel MVP "mona" <mona@discussions.microsoft.com> wrote in message news:02681B49-0A08-4CCA-A6B5-A33BCAEEBDBF@microsoft.com... >i have 100 > 200 > 300 > about 7 numbers and I would like to know how to add them. > =sum (g1-15)? ...

Indexing Values to Same Starting Point for Relative Return Chart
Hello all, I'm have a sheet full of data for various securities that is sorted b date. I can chart the values by date just fine, but I'm hoping t create a chart where each series is indexed to the same point and the the chart will show percentage changes. For instance, if A is at 30 and B is at 40 on January 1st, then in my chart I want them both t start at 100. So if A falls to 150 and B moves up to 80, then I woul want the chart to show them moving to 50 and 200, respectively. Do I have to manipulate the data in another sheet first, or is there way to do it with the charting t...

Can't sort column of SUM values
I'm having a strange data sorting problem where my table won't sort a column of values that are SUMs of other columns. For example, the formula in Row 2 in the sorting column H is: = SUM (A2, C2, F2, G2) When I do the sort of Col H, most of the numbers are in the right order, some of the sums are out of order. Not sure if it's just a coincidence, but it seems only the 11 sums that seem to get mis- sorted. 55 52 25 23 11 15 12 11 0 0 0 0 11 11 0 0 0 Anyone have any idea why this is happening?! Thanks. John If you're just sorting that column, maybe you can convert it to v...

Conditional Sum - Moving Average
Dear Expert, I have a question about conditional sum to calculate moving average. 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 ...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Getting the sum of several fields
I created a field called cash in a MS Access form, I want the totals for these fields 100 50 20 10 5 to be placed in that cash field. In the control source for the cash field I am using =Sum([100]+[50]+[20]+[10]+[5]) and thought that would work but it does not. I can do a sum of two fields, but anything larger does not work. What am I missing Dave I'm not entirely clear on the data structure underlying your form. Are you saying you have multiple fields ([100], [50], ...) and want to total across them? If so, you've just described ... a spreadsheet! Is there a reason you aren&...

how do I default sum to 0 for negative numbers in excel
One way" If(SUM(A1:A10)<0,0,SUM(A1:A10)) tj "ej764" wrote: > Try =MAX(0,SUM(F2:F300)) -- Don Guillett SalesAid Software donaldb@281.com "ej764" <ej764@discussions.microsoft.com> wrote in message news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com... > Hi =SUMPRODUCT(--(A1:A10>0),A1:A10) Arvi Laanemets "ej764" <ej764@discussions.microsoft.com> wrote in message news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com... > ...

Summing the contents of one column based on the contents of another?
Hi again everyone, Ok, after I got several speedy and very helpful responses to my last query, I figured I could do worse than asking for help here again! Maybe when I learn some more I can help dish out advice instead of just asking questions! Here's my problem: I have a large spreadsheet with data in columns A and B. Column A contains lots of strings. Column B contains numbers. eg: Column A Column B Piano 4 Saxophone 5 Clarinet 12 Piano 2 Piano 6 Clarinet 1 Ok, what I want to do is g...

Sum if Worksheet name contains certain data
I know =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) will get me the name of a worksheet. Is it possible to base a SUMIF formla on the worksheet name? I'd like to set up something that adds cell B23 for all sheets that contain the numbers 150 in the worksheet name. I've tried the Conditional Sum wizard, but it doesn't recognize the set of worksheets as a valid range StephanieH Wrote: > I know =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255 > will get > me the name of a workshe...

Need keywords and index in Office help file. Online help no good
Online help can be annoying unless you know the exact words and phrase to type. If your searching for help and unsure what to type you end up with lots of useless suggestions. It was alot easier to determine what help may be usefull when a person could view and index and keywords based on what you were currently typing in the index box. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this lin...