Sumproduct #Num!,#N/A (select Specific The Sum Of Various Lines Of Data That Fit Within 3 Parameters)
Mar 31, 2009
I am having a problem with a sumproduct strand. I've written 4 different ways to no avail. What I'm trying to accomplish is to select specific the sum of various lines of data that fit within 3 parameters
1.) =SUMPRODUCT((Sheet1!$H:$H="XYZ"),(Sheet1!$G2:$G136="Withdrawal"),(Sheet1!$I2:$I136="113183"),Sheet1! $K2:$K136)
Response: #NUM!
2.)=+SUMPRODUCT(--(Sheet1!$H2:$H136='Sheet2'!$A$5),--(Sheet1!$G2:$G136="Withdrawal"),--(Sheet1!$I2:$I136='Sheet2'!$B$5),Sheet1!$K:$K)
Response: #Num!.................
View 2 Replies
ADVERTISEMENT
Oct 3, 2011
So far I am capable in creating a loop that deletes a row with specific parameters (like font color is vbred). But this requires me to re-run the loop because the current approach I am using ("for each" loop) is causing the loop to jump over a row that I want to delete.
For example:
R1 To delete
R2 To delete
R3 To delete
R4 To delete
**After running the loop I still have
R2 To delete
R4 To delete
Is there another approach to identifying, selecting, and deleting the rows besides using a "for each" loop? Could I do this in one go?
View 2 Replies
View Related
Oct 1, 2009
How to get the value 0 if the formula can't find the specific parameters given below. What is the extra bit I need to add to this formula below?
=INDEX(Bayswater!1:65536,MATCH("Sim Only PAYM",Bayswater!A:A,0),4)
View 5 Replies
View Related
Nov 12, 2009
I have a SUMIFS function written that reads where some of my budget is coming from. I track several different departments but am only responsible for some of the departments' budgets. So i need to exclude two of the departments when the SUMIFS is reading the other parameters from my list.
From the example i attached, how would i go about writing the formula in I3 to skip the Cost if it comes from Dep 2 and Dep 4?
View 3 Replies
View Related
May 12, 2013
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
View 2 Replies
View Related
Aug 22, 2012
I have a very large spreadsheet that included a large number of SUMPRODUCT formulas. Many of them are running formulas that have SUMPRODUCT layered.
I have most of the SUMPRODUCT lines running 2 arrays to check for the date field. It sees if the date is >= the start date cell and <= the end date cell.
It runs fine, and works great, from a data standpoint. However, I am already running into slowdown when altering the date fields. The workbook is still in the starting stages and is going to get MUCH larger.
I have attached a sample sheet showing a little bit of what I am talking about. The SUMPRODUCT functions on sheet 3 all point to the start and end date fields, and the data updates as they are changed.
I need to find a way to condense the SUMPRODUCT lines. Is there a way that I could run one SUMPRODUCT line in a cell and then reference that cell for all of the other lines? Or is this just the best way to do this?
Streamline.xlsx
View 4 Replies
View Related
Jul 17, 2013
I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.
View 1 Replies
View Related
Apr 7, 2014
Select Specific Cells And Transfer This Data Into A New Row In A Different Worksheet, how can I do this in a macro?
I am trying to pull a select 6,048 rows out of 288,000 rows.
View 5 Replies
View Related
Mar 5, 2009
I am trying to select specific cells and transfer this data into a new row in a different worksheet. The code i am using i used sucessfully on a user form to take the entries from text boxes and place in a row in a new worksheet. I would like to try and do the same thing but on a worksheet rather than a user form. My code is
View 2 Replies
View Related
Aug 14, 2012
Is there any way to select specify month of the many years of data with any function?
View 7 Replies
View Related
Nov 6, 2008
Sample data (all in column a):
Heading 1
123
12345
1234
345
Heading 2
987
9876
Heading 3
785
Heading 1
2356
8525
Heading 2.........
View 9 Replies
View Related
Feb 3, 2010
How do you select specific coloured cells from a range of data in one go, without having to scroll through the worksheet and pick them out individually?
View 9 Replies
View Related
Nov 1, 2013
How we can select specific cells in the filtered data using VBA in Excel 2010.
I need to select 10th column, 5th row data .. or 10th column, 6th row data .. or 13th column, 8th row data...
How to select this data using VBA.. I am struck here .. If I give the below code.. the hidden cells inbetween the filtered data is getting selected.. only the first row is getting selected correctly.. i.e, ..rnVisible(1, 2).Select .. If I change the row like rnVisible(2, 12).Select its selecting the hidden cells ..
Dim rnVisible As Range
Set rnVisible = ActiveSheet.Rows("2:10000").SpecialCells(xlCellTypeVisible)
rnVisible(2, 12).Select
MsgBox ActiveCell.Address
[Code ........
View 1 Replies
View Related
Jan 3, 2013
I would like to reduce the size of a macro, but not managed to have success.
My goal is to just have the cells of these worksheets (in the code below), selected
Code:
Sub SumazeCode()
Sheets("PEDIDO 2013").Rows("42:67").EntireRow.Select
Sheets("Print_Cliente").Rows("32:45").EntireRow.Select
Sheets("Print_Producao").Rows("30:43").EntireRow.Select
Sheets("Print_Orcamento").Rows("32:45").EntireRow.Select
Sheets("PEDIDO 2013").Rows("C43:D43").EntireRow.Select
End Sub
View 2 Replies
View Related
Jul 7, 2009
I am trying to write a macro that given a selected range of cells goes in each cell and bolds the first 2 lines within the cell. An example of this would be if the cell contained:.............
View 2 Replies
View Related
May 8, 2014
macro that would keep all lines containing a number greater than zero in column D and delete all of the other lines?
View 9 Replies
View Related
Apr 15, 2014
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies
View Related
Mar 26, 2013
I have a table which is refreshed from a view on our SQL server. I use a part of this data further on and would like to automate the following: I would like to have a range set which I can use to loop through data. The range should hold the filtered data of table "PremiumTable", the columns "Grouping2", "Price", "Grower"
I have found a couple of posts giving the ability to create a range of visible cells and specific columns, but I wasn't able to combine this yet.
View 2 Replies
View Related
Jul 30, 2008
Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcard-type (pseudo) is needed to select all values when "ALL" is chosen?
I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula.
Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A
View 9 Replies
View Related
Aug 13, 2007
I have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
View 9 Replies
View Related
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Feb 11, 2013
How to autopopulate numbers from data sheet based on Selected month.
View 4 Replies
View Related
Jan 29, 2013
I am currently using this formula -
=SUMPRODUCT(--(MONTH(Leads!B3:B999)=1),--(YEAR(Leads!B3:B999)=2013),--((Leads!J3:J999)="Contract"))
This gives me the count of rows that have Jan as the month, 2013 as the year and Contract word in column J. Now I want to add another column to be checked. That column has values in format - Design: X, Design: Y, Design: Z etc.
So I want to have the condition above and the cells that have the word Design.
View 3 Replies
View Related
Mar 2, 2012
I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.
I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.
My non-dynamic formula is:
=RANK(GETPIVOTDATA("Target",$G$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),$L$8:$L$30).
I have figured out how to make some of the items variable to this degree:
=RANK(GETPIVOTDATA("Target",$G$5,"Company",$I8,"Product Name",$J8,"Sex","F","Age",25,"Class","1-BestNS","Scen Defn",$K8),$L$8:$L$30).
I know can make the $L$8:$L$30 copyable across columns by changing it to L$8:L$30.
My dilemma is several fold.
1. If I filter the Company or Product Name the $30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?
2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?
3. Similar questions related to the row labels for Sex and Age?
4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.
I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.
ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205
[Code]...
View 1 Replies
View Related
Aug 25, 2008
I've got two columns of numbers, such as:
(Col A) (Col B)
Cycle Bin
1 - - 3
1 - - 7
1 - - 7
1 - - 2
1 - - 5
2 - - 7
2 - - 9
2 - - 6
3 - - 7
3 - - 2
4 - - 2
4 - - 8
4 - - 2
4 - - 8
4 - - 5
I'm trying to get the total number of columns that have a specific match-up, for instance, how many cells are in Cycle 1 with Bin #7? ....
View 9 Replies
View Related
Mar 6, 2013
Excell file eWorked Example.xlsx
I am trying to set-up a formula that needs to be copied down and across and draws upon a data table that has a horizontal and vertical parameter that needs to be checked for the formula to return the right answer.
I have tried all manner of Index/Match, Offset etc but can only get one of either the vertical, or horizontal parameter working.
I have attempted a formula which is
=INDEX('PIP Input'!$C$17:$C$29,MATCH(H$1,('PIP Input'!$H$17:$H$29),0),MATCH($B4,'PIP Input'!$H$15:$L$15,0))
('PIP Input'!$H$17:$H$29) is column dependent on MATCH($B4,'PIP Input'!$H$15:$L$15,0)
If $B4 returned a different result from ('PIP Input'!$H$17:$H$29) then it would need to be ('PIP Input'!$I$17:$I$29)
How to nest the Horizontal Match inside the Vertical Match, such that the Vertical Match changes column depending on $B4?
View 1 Replies
View Related
Aug 21, 2009
I've got the following sumproduct formula (which seems to work):
=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?
View 9 Replies
View Related
Mar 26, 2013
How would you select all cells in a column that contain #N/A in them, all at once so I can delete them from my column?
View 4 Replies
View Related
Feb 24, 2009
I have an Excel 2003 spreadsheet, and in certain cells, I have a single letter. (e.g.) In A1, I have the letter A, in B1, I have the letter B etc.
I am trying to write a macro which will allow me to select a specific row, and depending on which letter is in which cell, set the cell directly below it to the numeric equivalent of the letter (e.g.) if in cell A1, there's the letter A, I want the number 1 in cell A2; and if in cell E5, there's the letter Z, I want the number 26 in cell E6 etc.
All I've got, is a VERY long macro, which will take row 1, and change each cell value to it's numerical equivalent. Here's an extract:
For Each cell In ActiveSheet.Range("A1:Z1")
If (cell.Value = "A") Then
cell.Value = "1"
End If..................
View 9 Replies
View Related
Sep 28, 2013
I plucked a macro off an old post from here (as I always do), but I'm having trouble tweaking it.
VB:
Sub completedtest()
Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("i"))
[Code] ....
As it currently stands, the macro selects the entire row of rows containing the letter Y in column I. All I need to change is, I want to simply select columns B through I, rather than the entire row.
View 2 Replies
View Related
