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

Double Condition, Or Else

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

Double Lookup Formula To Work With A Table

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

Range To Work On The Worksheet Before Double Click

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

Sumproduct Double Criteria: Put Total Qty Into The Defect Table

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

Sumproduct Formula And When To Use Comma's, Double Negatives, Addition

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

Automatically Copy Row Meeting Condition On Double Click

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

Sumproduct Will Work Only Up To Certain Columns?

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

How To Get Sumproduct To Work When Divisors Are Zero

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

How Does This Formula Work- Sumproduct

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

Sumproduct Given A Condition?

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

SUMPRODUCT Using A Condition

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

Sumproduct With More Than One Condition

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

More Condition With Sumproduct?

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

IF ELSE Condition With SUMPRODUCT?

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

Sumproduct With Condition Formating

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

Variable Condition In Sumproduct

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

Combine SUMPRODUCT With INDEX And MATCH But It Doesn't Work?

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

SUMPRODUCT Formula With Criterion Condition???

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

Format Condition Wont Work When Printing

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

Change Sumproduct Formula To Incorporate Condition

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

Sumproduct Returns A Zero In False Condition, Instead Of A Blank

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

Adding Condition To SUMPRODUCT Causes Result To Go Up? (2007)

Jan 22, 2010

Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:

View 5 Replies View Related

Excel 2003 :: How To Use List Or OR Condition In SUMPRODUCT

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

Adding Another Condition To Sumproduct (countif) Statement

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

Sumproduct And Sum Array Formula (calculate The Salary Payments To Temporary Employees In A Particular Work Unit)

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

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

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

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

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

Cell Equals 0 Changes When Condition Met - Leave Unchanged When Condition False

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

Multi Condition Format? Lookup And Date Condition Valid For

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







Copyrights 2005-15 www.BigResource.com, All rights reserved