SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined
Apr 4, 2014
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
[code]....
View 3 Replies
ADVERTISEMENT
Nov 13, 2008
i'm trying to perform an operation that gets the standard deviation of all open status. See my formula.
=SUMPRODUCT(($D$4:$D$1591="open")*1,($H$4:$H$1591)*1,(STDEVA($H$4:$H$1591)))
View 3 Replies
View Related
Feb 26, 2014
I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.
Example:
14T
16
40
20
150
97L
67
13
12
For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).
I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns.
View 10 Replies
View Related
Jul 15, 2006
Can someone help with this formula,
Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24
Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247
This is what I've got
=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))
View 9 Replies
View Related
Feb 11, 2012
The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))
B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.
Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).
The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.
I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]
View 5 Replies
View Related
Jun 18, 2009
I have the following line of code in the code for a textbox in a userform:
View 4 Replies
View Related
Mar 7, 2012
I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).
I have 52 colums ie one for each week.
=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000
View 7 Replies
View Related
Oct 29, 2008
i would to combined these together
can this be done
View 2 Replies
View Related
Apr 2, 2009
I have a sumproduct function picking up data from a different worksheet. Because i have to much data it takes too much memory and it doesnt run that smoothly. Therefore i have hardcoded the function and tried to evaluate it using the EVAL() function from the morefunc add-in. However i don't get it to work. It continuously returns #VALUE.
This is the code i am trying to evaluate
SUMPRODUCT(--('Retrieve Depletions'!$A$4:$A$5000=$D4),--('Retrieve Depletions'!$B$4:$B$5000=$E4),--('Retrieve Depletions'!$C$4:$C$5000=$F4),--('Retrieve Depletions'!$D$4:$D$5000=$G4),--('Retrieve Depletions'!$E$4:$E$5000=$I4),--('Retrieve Depletions'!$F$4:$F$5000=$J4),'Retrieve Depletions'!H$4:H$5000)
View 9 Replies
View Related
Dec 9, 2013
I have an INDIRECT function set up which should, in theory, concatenate data from a date range listed in another cell to correctly reference an external spreadsheet, which is named based on the date range it is generated for. The issue I'm running into is that the program which generates the external spreadsheet uses an MMDDYYYY format when naming its output file. This works fine if the month and day are both 10 or greater, but if they are less than 10, it doesn't work - for example, my INDIRECT function would return the date 5/3/13 as 532013, whereas the file name would be 05032013. I was contemplating using a series of IF statements to fix this, but there has to be a simpler way. Here are the relevant functions.
Function which generates date range (later used in INDIRECT function):
=MONTH(MIN(TransData!A2:A9882))&"/"&DAY(MIN(TransData!A2:A9882))&"/"&YEAR(MIN(TransData!A2:A9882))&" - "&MONTH(MAX(TransData!A2:A9882))&"/"&DAY(MAX(TransData!A2:A9882))&"/"&YEAR(MAX(TransData!A2:A9882))
TransData is a different sheet within the same workbook where the various dates are stored in column A. I found that I needed to format the formula like this in order to get it to return actual dates instead of date sequence numbers.
=AVERAGE(INDIRECT("'[DCQA_"&SUBSTITUTE(B4,"/","")&".xlsm]Total'!$C$3:$C$46"))
Cell B4 is where the date range formula listed above lives. "DCQA_" is the beginning of the filename of the external sheet, followed by the date range in MMDDYYYY - MMDDYYYY format. So, the sheet for 12/2/13 to 12/7/13 would be named DCQA_12022013 - 12072013. I'm unfortunately unable to change the way the external program names its output sheets, so I have to fix this from within my workbook.
View 2 Replies
View Related
Oct 25, 2013
I roughly know what my equation should look like, but it keeps failing. Its all part of a wider spreadsheet but I will only put down that bits that impact this part of the sheet.
In my cell I need it to input a value from a different spreadsheet. The input value is text.
I currently use this formula and it works perfectly for what I need.
=(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))
This would a return a variety of results such as "excluded","compliant","non-compliant".
I would like to arrange an IF function so that if the answer is Excluded to keep it as Excluded. But anything else to use this formula instead.
=(OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))
View 3 Replies
View Related
Nov 16, 2009
The following formula sorts for specifics in the sheet named 200910 in the specified ranges in columns A and D to return a total found in column AB. This works just fine.
=SUMPRODUCT(('200910'!$A$2:$A$1777="Countrywide")*('200910'!$D$2:$D$1777="Claims-All Products"),('200910'!AB2:AB1777))
What I am looking to do, instead of telling excel what sheet to go to, is insert this: =INDIRECT(TEXT(Y10,"yyymm")&"!ab1749") to find the matching sheet name to the date that resides in cell Y10.
These both work separately on their own to return the needed value. How do I put them into one formula without telling excel what sheet to go to (1st formula) and specifically what cell to go to (2nd formula) because the cell location may change and I want to completely automate this?
View 3 Replies
View Related
Apr 20, 2007
=SUMPRODUCT(--(A2:A1000=B1))
Column A has got random numbers, my formula attemts to capture the count of numbers as specified in B1, is there a way I can nest an indired t formula to mention in cell C1 ( > or < ) so it can look for numbers greater than the number in B1 or lesser as indicated
View 9 Replies
View Related
Dec 17, 2012
I am having an issue when I try a chart a named range. The named range "Refers To" is
Code:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)
&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
View 4 Replies
View Related
Dec 29, 2009
I have the following formula, which works; however I need to make it dynamic.
View 7 Replies
View Related
Jul 15, 2013
I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.
=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))
I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.
View 9 Replies
View Related
Dec 18, 2006
In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?
=SUMPRODUCT((INDIRECT("'attendance"&$A$3&"'!C:C")=$A$7)*((INDIRECT("'attendance"&$A$3&"'!I:I")<>0)))
In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?
View 2 Replies
View Related
Jul 12, 2006
I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.
1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.
2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.
3) Is it possible to use a vlookup function for this solution?
View 9 Replies
View Related
Mar 12, 2007
I have three pages of daily data I am working with; orginal source data, manipulated data, and my output. My output data is a daily row of six columns containing 1, 0, or -1, based on my manipulated data (1,1,0-1,1,-1). I would like by row, sum the coresponding source data (5,4,0,-3,3,-4) by the positve 1s and a second column with the negative 1s. then average the column by the number of consituents (positive5+4+3 )/3=4 & negative;(-4+-3)/2=-3.5)
View 8 Replies
View Related
Dec 27, 2012
In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.
On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.
Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.
I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?
Dynamic Range.xlsx
View 8 Replies
View Related
Dec 18, 2013
I can't work out why my indirect drop down list isn't working. I have named ranges and it works for the first couple but not for the rest.
Tab called 'Work record' is where the drop down lists are. 'Work Type' drop down works fine. Description only works when I select 'Doors' under 'Work type'.
View 3 Replies
View Related
Mar 5, 2012
How to add Sumproduct and Min/Max functions? Using Excel 2010
I am looking for the minimum, or maximum number within a range while using the Sumproduct function.
=SUMPRODUCT(--ISNUMBER(FIND("R",CMSB3003.xls!$A$12:$A$20000)),--ISNUMBER(FIND("Reg",CMSB3003.xls!$AF$12:$AF$20000)),
--(CMSB3003.xls!$B$12:$B$20000>=$B$2),--(CMSB3003.xls!$B$12:$B$20000
View 7 Replies
View Related
Aug 20, 2014
Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.
=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))
Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.
View 6 Replies
View Related
Feb 17, 2012
Reduced to it's simplest form I have a formula
=1*(FY2012_TY), where FY2012_TY
is a Defined Name which refers to a column on another tab in the workbook, and is defined as:
=OFFSET(Table!$AA$3,0,0,COUNT(Table!$AA:$AA),1)
What I would like to do is to come up with an approach to this formula that would allow me to drag it across columns and have the year automatically increment (e.g., FY2013_TY, FY2014_TY, all of which are similarly defined Defined Names referring to adjacent columns). I have tried various approaches using INDIRECT along with CONCATENATE, but when it comes down to evaluating the formula, I consistently get a #REF! error.
View 4 Replies
View Related
Feb 1, 2009
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.
New York (column header)
Wages
Lights
Net Profit
Boston
Lights
Wages
Sales
Net Profit
How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.
View 9 Replies
View Related
Apr 25, 2014
Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented"))
This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message.
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented",(INDIRECT("'"&MySheets&"'!E8"),"Partially implemented")))
View 1 Replies
View Related
Mar 25, 2014
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
Dynamic Ranges INDIRECT v2.xlsb
View 3 Replies
View Related
Dec 13, 2006
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
View 4 Replies
View Related
Feb 13, 2014
Is there anyway to add text to an INDIRECT reference so that it will now reference a new range?
For instance, this is my formula...
=IF(INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1)="","-",INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1))
I am trying to say, if the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS) "added to the text "TM", is blank, then write "-", otherwise return the number in the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS)+"TM".
Cell A1 is a data validation list that refers to different ranges. The only difference between those named ranges and the named range I want to reference is the text "TM" comes at the end.
View 3 Replies
View Related
Jan 17, 2008
I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.
The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.
Example: ...
View 14 Replies
View Related
