I would like to create a formula in D7 that calculates the weighted average growth for products (column A) manufactured by Bob (column B). In J5 I've inserted the expected result using a SUMPRODUCT function.
How to create a formula in D7 that will calculate from the data in B6:D6 the same result as in J5. i.e. How do I make the SUMPRODUCT function only consider data in columns C and D when Bob is the manufacturer?
I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.
A sumproduct would be best but the 2nd row of blue cells has an if statement in it.
Here is my dilemma I have 3 columns with data ranges two of which need to match certain criteria and if the criteria is met then I want the 3rd columns data to be sumed.
For instance: I want all only want to add column c for the ones in Column a that have blue and column b that have lost.
ABC
bluelost1
greenfound2
purplelost3
bluelost4
greenfound2
redlost3
orangefound4
I want to make some general tab from source in other sheet. I want to count unique product based more condition and also sum the amount for people and product.
It is better to understund in my atach.
I have a SUMPRODUCT formula that checks several columns of employee data for various conditions and sums up a salary value, which works just fine. But now I have to put a cap on the salary amount when summing up each employee, which I thought I could use an IF statement for, but alas just returns a #VALUE! error. Essentially what I tried was
Code:
=SUMPRODUCT( --( Range1=A1), --(Range2=B1), IF( SalaryRng>200000, 150000, SalaryRng) )
I thought since I was returning numeric values from the IF statement there shouldn't be a problem. Obviously not. So my question is whether an IF statement can be used this way, and if not, what method should I use? SUM/IF array formula?
I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.
View 3 Replies View RelatedI have the following formula
=SUMPRODUCT(INDIRECT($B3&"I3:I1000")*(INDIRECT($B3&"K3:K1000")>0)*(INDIRECT($B3&"u3:u1000")=$F$1)*(INDIRECT($B3&"p3:p1000")=$D$1)*(INDIRECT($B3&"r3:r1000")=$H$1))
Cells F1, D1 and H1 contain dropdown lists matching values in the referenced columns, PLUS they all contain the option All, which effectively means that the condition should return true.
I need something like if($F$1='All',1,(INDIRECT($B3&"u3:u1000")=$F$1))), but this doesn't work
see the attached spreadsheet. Basically I want the formula in the yellow cell to carry out a sum product of two columns, but only for rows where the value in the first (criterion) column is 10.
View 2 Replies View Relatedmy table is something like below:
my program sheet looks something like this:
I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.
date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)
date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2
I have have sumproduct formula that works well, however I need a section of it to use a condition.
"=SUMPRODUCT(--(('Input Sheet'!$A$10:$A$20009=3)+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1)=3),--('Input Sheet'!$L$10:$L$20009>=G21),--('Input Sheet'!$L$10:$L$20009<=J21))"
The first section ('Input Sheet'!$A$10:$A$20009=3) looks at column of data and checks if any of the data says 3, however I need the formula to look at the column of data and determine if the figure is =>3 and also =<5.
I've written a sumproduct formula, which does what I want it to do; but it returns a zero in false condition, instead of a blank. I've tried several things, but don't seem to be progressing very far. Here's the formula that I ended up with.
View 9 Replies View RelatedAdding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:
View 5 Replies View RelatedHow to include a boolean OR in my SUMPRODUCT formulas.
Software: WinXP SP2 and Excel 2003 SP3
I'll try to type in some data in legible format:
......Col B.....Col C.....Col D.....Col E.......Col F
1...NW OH....WMA.....110%....NW OH...(result)
2...NW OH....SIS.......130%
3...SO FL......PRB........92%
4...NW OH....TRO......104%
I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.
So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.
I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.
I have tried:
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),CALC_PERC)
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),--CALC_PERC)
Also tried both the above formula w/o the "--" in front of any array.
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE={"WMA","PRB","SIS"}),CALC_PERC)
Again, the above was tried with and w/o the combinations of "--" in front of arrays.
=SUMPRODUCT(--(CALC_AFFIL=$E1),--ISNUMBER(MATCH(CALC_TITLE={"WMA","PRB","SIS"},0)),CALC_PERC)
Once more with and w/o "--" combinations.
I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.
What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.
So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.
SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
I have the following formula's:
SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and
SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and
SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)
My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)
The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.
However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.
Can anyone help me so that the formulas continue from row 9 ?
I have the following formula:
View 4 Replies View RelatedI have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.
I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....
How to add another sum to this formula?
this is the formula i have at the moment
=SUMPRODUCT(--($AA$21:$AA$6376
Here's my question.
jan- feb- mar- apr- may
rep a 1 1 2 5 1
rep b 1 2 7 7 7
rep c 5 5 1 2 2
rep d 1 1 1 1 1
rep e 2 2 2 2 2
rep f 1 2 3 4 5
rep g 0 0 0 7 9
date date
March May
How do I set it up so that I will have two cells that will contain a month. Once the 2 months are selected it will give me the total for each rep during that time period? Do I use sumproduct?
I have a report with a macro that imports a .TXT file into Excel then calculates the follwing formula. For some reason, before the .TXT file is loaded the formula is fine and has no errors, then when it is run I get a "#NAME?" error. I thought it might be because the formula changed during the macro but that's not it. Examples are below.
I've looked online and tried naming the ranges, but that doesn't work. I've tried the built in help but that didn't help. I've tried making sure the analysis pak is installed but that's not it either.
I've ran out of ideas now and you guys are my last hope. Any clues...??
Before macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))
After macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))
=SUMPRODUCT(OR(MIS!$T$4:$T$2042>500000)*(MIS!$U$4:$U$2042>500000))*(MIS!$Z$4:$Z$2042=C2)
it is if the cell in Column T >500,000 or the cell in Column U is more than 500,000. I keep getting a value error and I can't find a way around it.
I'm working on a project on predicting future cash flows from loans we have made and have ran into a wall as i can't figure out the formula to achieve what i would like to do. I've gotten a lot of help from you guys in the past and would appreciate any help you may be able to provide for this problem. So here it goes. First, here is how i have the spreadsheet setup.....
View 9 Replies View RelatedI've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my
Function weight(Aref As Range, count As Integer, Bref As Range) As Variant
Dim i As Integer
Dim top As Integer
Dim vA() As Variant
Dim cA() As Variant
Dim vR As Range
Dim cR As Range
Dim count As Integer
count = Widgets.Rows.count
ReDim vA(count - 1)
ReDim cA(count - 1)
top = Aref.Rows.count
For i = 0 To top - 1.............................
I seem to get a sum of 0 when I use this sumproduct syntax: SUMPRODUCT(($R$77:$R$442="H")*($Q$77:$Q$442="OTE"),(D77:D442)). What am I not doing?
Below is an e.g. of my data
CDEFGHIJKLMNOPQRAdmin4444440 ...