Double Condition (where Sumproduct Won't Work)
Jun 30, 2008
my 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
View 16 Replies
ADVERTISEMENT
Jun 19, 2009
Have a quick one, trying to figure out a simple expenses/income/saving tracking table for my wife and in-laws; have figured out most of it. Will use pivot to extract most of the data, but on a summary sheet I need double condition sums (or at least I guess so...). For example I have "amount" column (which is expanditure) and date column (have used function to extract month and year for other purposes to separate columns), how can I calculate multiple costs (all the costs/expenditures in a month) that have taken place in eg. april 2008 only/ since I want to display it on a separate sheet as a summary overview...here's the link to imageshack, dunno if it's gonna work:
[url]
Have tried something like: =SUMPRODUCT(month="4")+SUM(amount) but it sums up the entire column...
or this one: =SUMPRODUCT(ISNUMBER(MATCH(4,month,0)+SUM(amount)))
I know I miss a bunch on the last one
View 9 Replies
View Related
Feb 19, 2010
I am having trouble getting the double lookup formula to work with a table. See attached sample.
View 2 Replies
View Related
Jan 19, 2010
I am encountering is that with my knowledge I can only get one range to work on the worksheet_before doubleclick function and thats the sort range.
I need several different ranges with each range giving a target value uinique to that range.
View 6 Replies
View Related
Apr 22, 2009
I use sumproduct to put total qty into the Defect Table. But it take a long time for excel to run the counting process. Problem: From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section
View 2 Replies
View Related
Jan 21, 2010
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
View 2 Replies
View Related
Jul 19, 2009
Split from Copy Rows, Meeting Criteria, From Multiple Worksheets & Append To Summary Sheet
This has been wrecking my head for a week. For the debtors tab it is all of the rows which have the value "Unpaid" for each of the 12 months that need to be copied into the tab. For the invoice I thought there could be a button at the end of each row and when you clicked it, it put the info into the invoice template and then the person could just print it off and resend it. And then do the same for the rest of the invoices.
View 4 Replies
View Related
Nov 3, 2011
I have a huge ss and my sumproduct function works only up tp certain columns and starts returning #value! error. here is the formula:
=SUMPRODUCT(--(Detail!$A$2:$A$2971>=$R$4),--(Detail!$A$2:$A$2971
View 4 Replies
View Related
Jun 10, 2014
How can I get Sumproduct to work when the divisors are zero? So I get a #DIV/0! if anything in DU89:DU100 is a zero in the below
=SUMPRODUCT((DU23:DU34/DU56:DU67*DU89:DU100))
I had thought
=SUMPRODUCT((DU23:DU34/DU56:DU67*DU89:DU100)*(DU89:DU1000)) would work but that gives #n/a
View 7 Replies
View Related
Oct 18, 2008
=SUMPRODUCT(--($C$1:$C$17="S"),--($C$2:$C$18"S"))
how does this formula work?
It counts the number of instances in a list but not sure how
does the ranges have to be differant for this to work? ie C1:C17 C2:C18
View 9 Replies
View Related
Apr 7, 2014
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.
View 3 Replies
View Related
Jun 20, 2014
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?
View 2 Replies
View Related
Oct 23, 2007
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
View 4 Replies
View Related
Mar 3, 2009
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.
View 4 Replies
View Related
Aug 7, 2013
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?
View 5 Replies
View Related
Nov 5, 2008
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 Related
Nov 25, 2008
I 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
View 9 Replies
View Related
Dec 15, 2013
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
Dynamic SUMIF(S).xlsx
View 7 Replies
View Related
May 8, 2009
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 Related
Nov 12, 2008
i have set some cells with format condition, so when the value of it is true the entire cell should be gray "font,border,pattern". so the data will be invisible. so it dose that but when i print the font is back to black? which should be gray? i dont know why its doing that.
i tried this in other computers some of them work ok and most of them dont?? maybe its the printers? if so whats the solution?
View 9 Replies
View Related
Mar 3, 2014
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.
View 5 Replies
View Related
Jan 8, 2007
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 Related
Jan 22, 2010
Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:
View 5 Replies
View Related
Oct 30, 2009
How 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.
View 7 Replies
View Related
Oct 6, 2011
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&"*"))
View 5 Replies
View Related
Jul 16, 2009
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
View 2 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Mar 24, 2014
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.
View 2 Replies
View Related
Aug 13, 2009
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 Related