Conditional SumProduct To Evaluate Iteratively
Sep 18, 2009
I have two tables, one summary sheet which contains a list of 30 projects. The second sheet has the projects broken down by task and status ("on time" or "late"), so it has three columns (project, task and status). I am trying to add up the number of projects within each task that are either "on time" or "late" and put these in two columns, so that the sum of each together will always equal 30.
The trouble is that any given task can appear multiple times for a project, and if one instance of that task is late, I want to count the whole project late for that task. Here is a table of what I am trying to do:
Project Task Status
A1 MU On time
A1 MU Late
A1 FA On time
B2 FA On time
B2 MU On time
B2 MU On time
The desired result of this scenario is that under task "MU" I would show a count of 1 projects on time (B2), and one that is late (A1). For task "FA" I would show a count of 2 projects "on time" (A1 and B2) and none for "late"
I think that an array formula is needed where it assigns a 1 to a project and task that is on time, and a 0 if it is not, and then multiplies these figures for each project and adds them up.
View 9 Replies
ADVERTISEMENT
Feb 5, 2009
I cant see the wood for the trees! I have this formulae which works just how I want
ACO2 = Evaluate("= SumProduct((K2:K3=""Closed"") * (EG2:EG3=""06-15 Days""))")
However when I change this to a dynamic range I keep getting a syntax error. Can anyone see where I have gone wrong? I know its probably only a " or an ) out of place but its driving me mad!!!
ACO2 = Evaluate("=SumProduct(((Range("K2:K" & Lrow)=""Closed"") * ((Range("EG2:EG" & Lrow)=""60 Days+""))")
AC02 is an integer, as is Lrow (its a simple loop which works out how many rows there are).
View 3 Replies
View Related
Dec 10, 2013
I've been trying to get this SumProduct formula to return anything except an error for a while now.
=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17
View 2 Replies
View Related
Sep 28, 2007
I'm having trouble figuring out how to evaluate the result of Conditional Formatting using VBA.
I've got a series of cells ( dates) that are formatted in BOLD when a corresponding cell elsewhere in my spreadsheet shows that the date is Actual and not Projected ("A" vs. "P").
The Conditional Formatting works fine, I just can't figure out how to get VBA to skip the cell if the value is not BOLD based on the Conditional Formatting result.
ex:
For Each i In Selection
If i.FormatConditions(1).Formula1 = True Then
'OR i.FormatConditions(1).Font.Bold = True Then
i.Copy
i.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
End If
Next i
View 6 Replies
View Related
May 11, 2014
I am trying to find the function or code in order to loop through and name each cell in a range iteratively.
For Example,
Cells A1,A2,A3,A4 would need to be renamed
so in psuedo code
name cell a1 ag.1
name cell a2 ag.2
name cell a3 ag.3
name cell a4 ag.4
Most of the naming is done in string literals which I am not sure how to get passed using a loop.
View 2 Replies
View Related
Apr 13, 2009
I have a very large spreadsheet (about 50,000 rows, and to CM of columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data. I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet.
In faux-
For row i
i = 1 to 50,000
If Column B = #NA
Delete row i
Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction?
View 4 Replies
View Related
Nov 7, 2012
I'm trying to build a macro that iteratively runs a goal seek, then reduces a certain value (Let's call it "Bogey") in a different cell by a small amount (note that changing Bogey will affect the desired goal seek value), then runs the goal seek again, reduces Bogey by a small amount again, etc etc, and keeps running in this loop, with the restriction on this loop being that a different cell, affected by Bogey--let's call it "Constraint"--doesn't drop below a certain amount.
View 5 Replies
View Related
Jan 15, 2010
I'm trying to understand the consequences of conditional arguments for the function SUMPRODUCT(). For example, this function was kindly provided by SGH:
View 2 Replies
View Related
May 8, 2008
I want to know the formula which sums up volume and multiply it with given price to get the value by category. An example is given in the encl. excel file.
View 3 Replies
View Related
Nov 1, 2013
I need to sum several non-contiguous columns if certain other columns meet a certain criterion.
Details:
Need to sum columns Q,R,S,T,U,V,W,X,Y,Z,AC,AD,AI,AJ,AK,AL,AM,AN,AP,AQ,AR,AS,AT,AU,AV,AX,AY,AZ,BA,BC,BD,BE,BF,BH,BI,BK,BL,BM,BN,BQ,BR
in sheet "(1) 65539004 SAP"
IF
column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = $B$1 in "(4) Brand Breakout"
The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,
View 9 Replies
View Related
Mar 2, 2007
Is it possible, when user types for example "1+1" in cell A1, to calculate this sum in cell A2?
So I would have "1+1" in A1 and "2" in A2.
View 9 Replies
View Related
Nov 27, 2008
I cant figure out why this "evaluate" function is not working as I expected. I have number/text from $A$1 to $A$5 to try and learn how to use this function but have not had any luck. I need to learn how to use it properly so I can use a for loop to change multiple check boxes on a user form that I am trying to make.
View 8 Replies
View Related
Dec 16, 2006
I need a way for tell Excel to NOT evaluate a formula. Basically, I want Excel to leave the value being displayed in the cell alone. The formula in the cell is a function that I have implemented in C++ and registered with Excel through the Register call.
View 9 Replies
View Related
Jan 26, 2007
I cannot figure it out for the life of me. When I type a formula into a cell it will not evaluate, instead it just displays the text of the formula.
When I go to evaluate the cell -- Tools | Formula Auditing | Evaluate Formula
It tells me that "the cell currently being evaluated contains a constant."
What do I need to change so that cells will evalute formulas I put into the?
View 9 Replies
View Related
Jul 3, 2008
Need the syntax for using a checkbox in a col. ?
If checkbox checked >> Syntax ??
If un-checked >. Syntax ??
Does it simply evaluate to val. of cell to either 'Y' or 'N' ?
View 9 Replies
View Related
Oct 8, 2008
I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.
I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.
I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
View 7 Replies
View Related
Oct 29, 2009
I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs:
i.e.
Numbers Prefix Country
4476324125 44 UK
3354326544 33 France
9713425432 971 UAE
9143253245 91 India
1343543253 1 USA
4432412412
4924241214
7431242424
So I would need to add a column next to "Numbers" saying to which country each number belongs.
My list has a few thousand numbers and a couple of hundred prefixes.
I tried with some array formula, but cannot make it work.
View 11 Replies
View Related
Nov 17, 2009
Example numbers:
21130 & 21065
I want to check each number if EITHER of the two conditions is true:
1. if the third digit from the right (the hundreth place) is greater than zero;
or
2. if the second digit from the right (the tens place) is >=6.
If either is true I want to add a particular number to the original number.
My example numbers meet questions 1 & 2, respectively.
View 11 Replies
View Related
Jul 2, 2014
I have to run an Evaluate function which is linked to an Array formula, but when I try just to put =ev(), the Array Formula which is located in another cell, does not run properly.
View 3 Replies
View Related
Feb 11, 2010
How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?
View 2 Replies
View Related
Jul 9, 2008
Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"
I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.
I have attached a test file which should clear things up
View 9 Replies
View Related
Mar 11, 2013
I have a problem in getting VLOOKUP to evaluate consistently. It is best understood from the screenshot linked (Office 2010), where cells B3 and B4 are not showing the expected values. I wanted to upload the excel file, but cannot see the option to do it.
The screenshot is at Book2 | Flickr - Photo Sharing!
This is just a small segment of the big excel sheet.
View 6 Replies
View Related
Jul 28, 2013
If I copy "mysum" contents to a cell and replace "lista" with a defined list, it works just fine. But whenever I run this code, it gives me syntax error.
Sub aaa()Dim i As Integer, myvar As Variant, tester As VariantDim mysum As Variant, lista As Variant,
alpha As Datelista = Sheet2.[D6].ValueWith Worksheets(Range("A10").Value) Do
i = i + 1mysum = Evaluate("SUMPRODUCT(((ISNUMBER(MATCH($B$8:$B$10007," & lista & ",0)))
*($A$8:$A$10007>(TODAY()-180))*(($E$8:$E$10007)+($F$8:$F$10007)+($G$8:$G$10007)+($I$8:$I$10007)+($K$8:$K$10007)))))
myvar = Evaluate("=TIME(10,0,0)>" & mysum) Loop Until myvarEnd Withtester = 35 - Sheet2.[c10]
alpha = mysumMsgBox "VALID for [" & lista & "] after " & i & " Day(s).
hours in last 180 days after " & i & " Day(s) will be (" & alpha & ")"End Sub
View 3 Replies
View Related
Jan 22, 2009
Ok, this should be an easy one. I have a formula that one of the functions needs to check if certain cells are blank.
For example; I want to check if A2, B2 and C2 are blank.
Currently, the only thing that seems to work is,=IF(OR(ISBLANNK(A2),ISBLANK(B2),ISBLANK(C2)),"PASS","FAIL")
This is just a piece part of the entire formula and I have to evaluate the data based on multiple criteria. The ISBLANK() portion has to be able to evaluate at least 15 cells. Is there a way to evaluate the cells all at once instead of typing out ISBLANK() over and over. I have tried everything I can think of in regards to syntax.
View 9 Replies
View Related
Jan 17, 2007
I have 2 sheets (book1.xls and book2.xls) book1 has over 20 sheets and has my primary data. Book2 is for a report. The report I want to only display one sheet at a time, by me typing in which sheet to access.
What I want to have:
A2 = Sheet Name
C2 = ="'C:Excelfiles[book1.xls]"&$A$2&"'!$B$1" C2 should have the value from B1 on 'Sheet Name' from the file book1.xls
however, all C2 will display is the text, I cannot get it to actually do the reference. (appears as 'C:Excelfiles[book1.xls]Sheet Name'!$B$1 )
Is there a function to say, evaluate data output from another cell?
(less complicated example)
A1=1
A2=2
A3=A1+A2
A4=3 (formula to evaluate output of A3)
View 5 Replies
View Related
Jul 14, 2013
I am trying to perform a count statement in VBA as below, with the 1st Evaluate I get the correct response, however with the second option it shows a 0. The variable is obtaining the correct values as per the example of 6017Complete, this is proven with the MsgBox
VB:
Dim TillNo As String
TillNo = Target.Offset(, -1) & Target.Offset(, 0) & "Complete"
MsgBox "Till Number is" & TillNo
Target.Offset(, 10) = Evaluate("COUNTIF(C3:C123,""6017Complete"")")
Target.Offset(, 11) = Evaluate("COUNTIF(C3:C123,TillNo)")
View 6 Replies
View Related
Oct 27, 2013
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
View 7 Replies
View Related
Jun 9, 2014
I have a ss that has item descriptions, quantities and pricing.
Item descriptions are identified by a letter (a, b, c, etc) and in cells C20:c32. These are selected by drop down box. Item quantities are in cells E20:E32.
I want to evaluate cells C20:C32 and determine what letter is chosen. If A is selected in any cell C20:c32 I want to count the quantities for A in cells E20:E32. I can't quite figure out how to do this.
View 2 Replies
View Related
Jan 7, 2014
First, I tried to input Ibs, Size, Items into a worksheet with layout.
Then, calculate how many items are they and separate them into two categories - "Big" and "Small".
Final, I would like to create another worksheet to evaluate how many items belongs to "Big" and "Small" with a column chart to present.
Question : How can I accrue the total of items and sort them into the right categories automatically.
View 3 Replies
View Related
Aug 26, 2009
I am trying to use an If statement and wanting to evaluate another formula.
The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6.
Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
View 4 Replies
View Related