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
759 Views

Similar Articles

[PageSpeed] 34

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:

Summing random days of the month
I have a workbook with 32 sheets, 1 – 31 and a SUM tab. On the sum tab I would like to calculate how many times a sequence of numbers (ex. 1102, 1235, etc) appears, however, I do not necessarily want to count it each day of the month, so on the sum take I have a column for start date and end date. How do I create a formula that references the start and end days, then sum every time that 1102 appears on those days / tabs? Here's an example of how my Sum tab looks: Unit # Start Date End Date SUM 1102 1 15 1235 5 10 1511 2 22 Thank you....

Summing negative time
Hi All, I am having problems adding time values hwen a negative time is involved. I have read all the posts on this and have chosen the 1904 date option, but no success. I determine the difference between times and then subtract a standard day from this amount to achieve a result (positive or negative). Then I want to sum the results. Works fine as long as I don't have any negative results. If I do have negative then the answer is always 0. Example: Morning.............Afternoon..............Balance In.......Out........In........Out..........Total..Flex...Balance 8:15...

Group By Query
Hello All, I am trying get a sum by using a group by query. However, the query is not handling any negative numbers I have in the fields. For instance Record 1 = $200; Record 2 = $300; Record 3 = ($50) .... The sum I'm recieving is $500.. NOT $450 Is there anything I can do to remedy this problem? Many Thanks! Hi - Try identifying negative numbers as -50 rather than (50). Example from the debug window: record1 = 200 Record2 = 300 Record3 = -50 ? record1 + record2 + record3 450 HTH - Bob TechTutors wrote: >Hello All, > >I am trying get a sum by using a group by query....

What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula?
I have written a macro that allows a user to select a specific series on an embedded chart, then specify a change to the series limit ranges. It works very well, EXCEPT I have not figured out how to write a statement that will return the index number of the selected series. I mistakenly believed that the series index number is the last number on the right end of the series formula. It's not. That's the plotting order sequence number, and it can change. I need the series index number, which I think is unique within the workbook. I tried using SELECTION.NAME after selecting the se...

Totaling Sums for Price and COMM
I am trying to find a way to total Sums for two Fields, Price and COMM. This must be an easy fix, just haven’t done this in a while and I think I forgot a step. In my Report, I have the following: Page Header, I have TDATE (for TradeDate), CUST (for Customer), B/S (Buy/Sell), C/P (for Call/Put), Quantity, Symbol, Price, and COMM. In CUST Header, I have CUST In Detail, I have TDATE, CUST, B/S, C/P, Quantity, Symbol, Price, and COMM. In TDATE Footer, I have =Sum([Quantity]), =Sum([Price]) =Sum([COMM]) Now, I’m trying to figure out how to sum those last two values; Sum all ...

Add data Sum in several sheets
Could some kind person assist with this? Main data on a range is split by macro into several worksheets and each worksheet get named as the result of the filtered split (i.e. if column A of main data is entered with various rows cat dog rat there will be worksheets called cat dog rat containing original rows from main data sheet. If say another data line is out into Main data sheet with column A cell called Monkey a further running of the macro is needed that adds another worksheet called Monkey and so on. It is required, as next step, to total each of the worksheets putting a double ...

Sum Value using ALT+ENTER
i have enter the record in the row, and inside the cell, i'm using ALT+Enter to make more line for the record, but i would like to know how to sum of the value in this cell?? e.g. C1 C2 C3 R1 1 1 XD R2 2 2 YY R3 1 XY R3 2 A R4 3 3 how can i get the value of A = 3, that is the sum of R3C1(1+2) also, how can i put the dollar sign automatically in cell R3C1 if i'm using "ALT+ENTER" to make more line. -- kilianli -------------------------------...

=Sum(Abs) and Date Range Question
In a report I have 2 simple text boxes. One is =Sum(Abs([First_Level_Appeal_Determinations]="Denied")) and the other is =Sum(Abs([First_Level_Appeal_Determinations]="Granted")) Both are from the same field in the same table - no query involved. I want the user to be able to input a date range and then run the report to count the number of each criteria. How would I do that? -- Thanks knowshowrosegrows schrieb: > In a report I have 2 simple text boxes. One is > =Sum(Abs([First_Level_Appeal_Determinations]="Denied")) and the other is > =Sum(A...

Sum duplicate lines in a table
I have a table which contains a list of products soted by poduct name. Against each product there is a stock quantity, each Item may appear more than once. I want to be create a query which will give me a list of the products, but with only one entry per item, with a total quantity for each item. Is this possible? Thanks Neil wrote: >I have a table which contains a list of products soted by poduct name. >Against each product there is a stock quantity, each Item may appear more >than once. > I want to be create a query which will give me a list of the products,...

Chart Legend Color Index Assignment
Looking for VBA code to run on Workbook_Open to ensure a Legend having up to 10 different variables does NOT "automatically" assign to same color to 2 different variables. TIA - Bob Check out the color palette (Tools menu > Options > Color tab). The chart uses the bottom two rows of colors for its data, then when it runs out it cycles through the rest of the palette. If the palette has been customized by someone unfamiliar with this behavior, you may get duplication of colors. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTe...

Deleting formula in cell if sum is 0
Hi, Was wondering if there is a macro which would be able to identify that the sum of 2 cells is 0 and therefore deleting the formula? Any help would be greatly appreciated Something like Sub Test() Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then If cell.Value = 0 Then cell.Value = "" End If End If Next cell End Sub -- HTH Bob Phillips "Aloysicus" <aloy33@singnet.com.sg> wrote in message news:uSeSxvWSFHA.2680@TK2MSFTNGP09.phx.gbl... > Hi, > > Was wond...

Summing times.
I know this should be easy, but it doesn't seem to work: Cell formatted in Custom h:mm. I use =SUM(L3:74) with L75 same h:mm format. It seems to skip or miss some. What am I doing wrong. Mike Change your custom format to: [h]:mm The square brackets prevents the hours from rolling over to days. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Mike Klick" <mike@grayce.net> wrote in message news:uITZJfo%23DHA.2664@TK2MSFTNGP09.phx.gbl... I kn...

IF statement / SUM IF
I have a spread sheet with the following: In Cloum A are 4-digit division numbers, In column B are 5-digit account numbers, In cloumn C are values. I am attempting to write a formula that says: If the range in column A = 1234 and Cloumn B = 12345, then Sum the value in Column C, if not zero. Any help would be greatly appreciated. Thanks. T.R. =SUMPRODUCT((A1:A15=1234)*(B1:B15=12345)*(C1:C15)) I didn't include your "if not zero" condition because if the number is zero, it won't change the sum anyways. Dan E "T.R." <anonymous@discussions.microsoft.c...

Problems Summing
I have a database with a few relationships in it. The patient and their dependants are in two separate tables and a rate of Fee is in another. I have a report which lists the patient and fee with their dependants listed underneath. However, when try to put a sum field in the report header it adds the fee for each dependant. How do set it up to add the fee once per patient? Sum it in the footer. controlsource=sum([patient]) -- Milton Purdy ACCESS State of Arkansas "Caryn" wrote: > I have a database with a few relationships in it. The patient and their ...

use of sum by skiping one or more cells #2
I want to sum only even cells from A1: A1000 I used =a2+a4+a6+a8+…………..+a1000 Used this formula n cell b1 But now I want to solve this problem by some short formula Help me thank you Try this: =SUMPRODUCT((MOD(ROW(A1:A1000),2)=0)*(A1:A1000)) Faisal Yameen wrote: > I want to sum only even cells from A1: A1000 > I used =a2+a4+a6+a8+..............+a1000 > Used this formula n cell b1 > But now I want to solve this problem by some short formula > Help me thank you ...

How to sum up the value in column based on a 5 day week period?
Hello, Im trying to add the amount of time spent on a type of machinery based on a 5 day week period. For example from 3/1/2010 to 3/5/2010 not including weekends. On the sheet with the data of the amount of time I need added is layed out like this: Colomn A Colomn B Colomn K Example:3/1/2010 PL 1 1:54 3/1/2010 PL 5 0:13 3/1/2010 PL 1 0:07 3/2/2010 PL 5 0:13 3/2/2010 ...

Sumif and Index
Hi, I am new to this, and desperately need some help. I am trying to put together a workbook that compares multiple data sets. However, I have two variables that specify what information I need. I have two dropdown boxes. One is to specify the group of departments and the other is the month that I want to look at. The group of departments is a total of subdepartments. For example, there are 4 departments A,B,C,D that roll up to group A2 and three departments F,G,H that roll up to group A3. In my dropdown box, I specify that I want group A2 to be evaluated. When I choose A2, I sumif the 4 dep...

CountIf and Sum Help???
Within a macro I am trying to add 2 formulas to the bottom row of my worksheet. The code is below and has allowed me to add static data. Now I need to do a count or countif (or something else)for column c that will give me the total number of rows that are "01". And for the final column i want to do a sum. Can anyone help? 01 150080380549 0000718649 000000095556 01 150080380549 0000718650 000000012000 01 150080380549 0000718651 000000002080 01 150080380549 0000718652 000000002912 02 150080380549 ???? ??? ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Off...

macro to sum with offset in 2 cell as other cell changed
I need to create macro with this scenario: F - quantity H - 1 serial I - 2 serial For example some cells: H644 =SUM(I643+1) I644 =SUM(I643+F644) So when I put some number in cell F644, sums correctly calculated, then one by one cell as i enter some in F Thank you. is this macro correct for this operation? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then With Target If .Value <> "" Then ..Offset(0, 2).Value = Cells(Target.Row...

Summing every nth cell
Is there some way to tell Excel to sum (or whatever calculation) every nth cell in a column? I'd be most appreciative of any suggestions. One way: =SUMPRODUCT((B3:B20)*(MOD(ROW(B3:B20)-ROW(B3),N)=0)) Where N=2, 3, 4, ............ -- Best Regards Leo Heuser Followup to newsgroup only please. "S. Neese" <neeses@cochise.edu> skrev i en meddelelse news:468101c42bcf$ceb46510$a601280a@phx.gbl... > Is there some way to tell Excel to sum (or whatever > calculation) every nth cell in a column? > > I'd be most appreciative of any suggestions. Hi try the fo...

sum and devide with not null value
Dear Masters, Please help me how to get this result D E 2 110 - 3 110 102 4 80 - 5 66 - 6 82 75 7 70 - result : 1.84 =(E3/D3)+(E6/D6) but I mean not manually, maybe any Masters can help me.. Thanks in advance.. You need to describe your problem more clearly. e3/d3+e6/d6 does not result in 1.84. What specifically are you looking for? Regards, Fed "Are" <Are@discussions.microsoft.com> wrote in message news:1B19E58B-C87C-47B5-BC4D-AF701...

#VALUE! When Attempting To SUM A Funtion that Does Not Print "FALSE"
Here's what I'm trying to do. I have a few rows of cells executing a function that inserts a dollar amount when "Yes" is entered in the adjacent cell. Here's what the cell looks like COLUMN I COLUMN J "YES" =IF(I9="YES",15.99,"") "YES" =IF(I10="YES",15.99,"") "YES" =IF(I11="YES",15.99,"") As you can see I did my best to avoid Excel from printing "FALSE" when the cell is empty The problem is that I'm trying to sum the dollar amounts in COLUMN J using this function...

INDEX/MATCH update limitation
I was very happy when I found the INDEX/MATCH formula. However, as MATCH and VLOOKUP formulas seem to be able to udate themselves, even though the source document is closed...INDEX/MATCH formulas seem not be able to update when the source document is closed. I am using Microsoft Excel 2003. Am i doing something wrong, or is it simply impossible? Hi, The problem is with INDEX - in Excel it doesn't work with external files that are closed. It works with closed files in Lotus 1-2-3 even back in 1982. And in Open Office today. -- If this helps, please click the Yes button. Cheer...

Sum of large minute/second figures
I'm trying to total a column with large minute values. For example, I want to add 0:1125325:27 (1,125,325 minutes, 27 seconds) with 0:2358964:18 (2,358,964 minutes, 18 seconds. My total column shows as 00:00, no matter how I play with the format. I have the individual times formatted as h:mm:ss, and I have the total column formatted as [mm]:ss so the results will stay in minutes. However, it's almost like I need to have h:mmmmmmm:ss and [mmmmmmmm]:ss. However, that's not working for me. If I try to create h:mmmmmmm:ss, Excel saves it as h:mmmm:ss, and then it reads the '...

SUMIF/AVERAGEIF with mulitle range and sum ranges
Im getting an #VALUE! when putting in this formula =AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$134:$C$176,$C$198:$C$240)) I know I'm using it wrong, please help! You can't use muliple range references like that with AVERAGEIF. Kind of long (but not as long as it could get!): =SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262),CHOOSE({1,2,3,4},C6:C48,C70:C112,C134:C176,C198:C240))/SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262)) -- Biff Microsoft Excel MVP &qu...