Sumproduct Where Two Conditions In The Same Column
Jan 16, 2009
I am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero.
Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So:
=sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C))
=total cost of Project1 when both Joe AND Mark work on it.
Unfortunately, when I structure sumproduct this way, it returns zero.
View 9 Replies
ADVERTISEMENT
Jan 6, 2009
I have 4 columns, A B C and D. They are a mix of formats.
A= Name (text)
B= Date
C= Code (text)
D= Number
I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.
I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.
View 14 Replies
View Related
Jan 7, 2009
I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.
I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).
=SUMPRODUCT((C3:C124=1)*(D3:D124=0))
View 2 Replies
View Related
Apr 10, 2008
My sumproduct has multiple conditions - is there a limit to the number of multiple conditions one sumproduct formula can have? I didn't think there was????
The formula looks like this, and should return results - at the moment, it returns #N/A. Does it have anything to do with the fact that I'm using named ranges?
=SUMPRODUCT(--(Data!W:W>=Cumulative!A12)*(Data!D:D=Super)*(Data!E:E=Region)*(Data!Q:Q=EWC)*(Data!J:J="H")*(Data!L:L="Tonnes"),Data!K2:K65536)
View 9 Replies
View Related
Feb 8, 2009
I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.
Like this one, which counts rows where conditions are met in four columns:
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5))
and this one, which sums the values of the same
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))
How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?
Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).
I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.
View 2 Replies
View Related
Feb 16, 2006
Ive started using the sumproduct function to count multiple conditions which is useful
howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?
the only way i can think is like the below
=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))
Rather than having to eliminate red and yellow i would like to say is green or blue.
View 14 Replies
View Related
May 7, 2012
I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?
View 3 Replies
View Related
Dec 6, 2009
Can a sumproduct formula accomodate multiple criteria?
The following is a sumproduct formula, for just one condition.
SUMPRODUCT(--(A1:A100="Red Sox"),--(B1:B100""))
View 9 Replies
View Related
May 6, 2009
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".
In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View 3 Replies
View Related
Jan 30, 2008
in column a I have dates and in column b I have names.
eg
A1 = 1/1/08
A2 = 2/3/08
A3 = 3/1/08
A4 = 3/1/08
B1 = Jenny
B2 = Jenny
B3 = Jenny
B4 = Pat
I am trying to count the number of instances of "Jenny" in January.
I tried =sumproduct(A:A,>=39448
View 9 Replies
View Related
Dec 6, 2013
I am trying to sum up cells from a column based on conditions in another column (see attached file). I know this could be done using a formula but for various reasons I want to use code. I am not sure if what I am doing is the right approach.
View 1 Replies
View Related
Jul 21, 2009
=SUMPRODUCT(C1:C3,1/(D1:D3))
Trouble is sometimes the value in D1:D3 could be 0 or nothing. how do I get the formula to only sumproduct the rows where the value in Column D is not a 0?
I tried the following
=SUMPRODUCT((D1:D3<>0)*C1:C3,1/(D1:D3))
View 4 Replies
View Related
Oct 26, 2009
I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.
The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".
The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.
View 3 Replies
View Related
Feb 10, 2010
I use the sumproduct for the attached example. I know I have seen this somewhere on the forum where I can get a value based on a criteria from a row and criteria from a column, but I just can't seem to figure it out right now.
View 2 Replies
View Related
Aug 12, 2008
How do I use a sumproduct for a column with the words "YES", "NO", or "MAYBE" appearing?
I'm using
sumproduct(--$C$1:$C$50000="YES"),--($C$1:$C$50000="NO"),--($C$1:$C$50000="MAYBE"))>1
View 9 Replies
View Related
Feb 15, 2014
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
View 3 Replies
View Related
Apr 24, 2009
I'm trying to work out how to fix the formula below to take into account and ignore and text entries, while giving me the result of the sum of column K minus the sum of column J. If I delete the text entries, the code works but I need the text entries to stay where they are. I've attached a sample sheet with fake info to explain whan I'm trying to do.
Cell N28 on the 'MGMT INFO' tab contains the following formula:
=IF(ISERROR(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))),0,(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))))
Columns J and K on the 'Sheet01' tab contain the Pay and Invoice information for all the work planners for that client that I'm trying to find the difference between. Each work planner has 'Pay' and 'Invoice' also in that column though, one entry per planner which is causing the SUMPRODUCT formula to screw up.
View 3 Replies
View Related
Sep 24, 2012
I have two sets of criteria I want to incorporate into one formula. In the first column, if the criteria is matched, it will check the criteria in the next column. The criteria in the second column is something that resembles the 'or' function. So if criteria equals x,y, or z, sum the results from the data range c3:c98. I tried writing it like this.
=sumproduct(--(a3:a98=a),--(b3:b98=x(or(b3:b98=y,b3:b98=z))),c3:c98)
View 1 Replies
View Related
Jul 17, 2013
I have this:
=INDEX(SUMPRODUCT((D:D>=DATE(2013,6,6))*(D:D
View 1 Replies
View Related
Jan 24, 2008
I am trying to sum up the rows that have multiple values in one column.
Here is my curent formula THAT works
=SUMPRODUCT(($H$46:$H$5787="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium"))
Now I also want to add the following
($K$46:$K$5787="Assigned")
How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?
View 9 Replies
View Related
Jul 29, 2009
I've created a spreadsheet with SUMPRODUCT formulae, which is working fine for now.
However, these formulae include arrays with ranges of, for example, $G$2:$G$600. What we need to do is, instead, reference the while column as far down as it goes, forever as the range of the array. This applies to multiple occurrences.
Every formula I have found for this may work on of itself, but does not work with the SUMPRODUCT formulae I have used.
For reference, an example:
=SUMPRODUCT(--('BG NEW DB'!$D$2:$D$600=$B$2),--('BG NEW DB'!$G$2:$G$600=C$2),--('BG NEW DB'!$H$2:$H$600=$A$2),--('BG NEW DB'!$AD$2:$AD$600="Y"))
View 10 Replies
View Related
Apr 11, 2014
I have a criteria issue with a sumproduct formula i'm using. The criteria is essentially supposed to say "if the value in column I (in Sheet2) equals ANY value in column O (in Sheet1), then do not include that line in the SUM. Here is the complete formula:
[Code] ............
View 1 Replies
View Related
Apr 11, 2014
I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...
That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.
View 1 Replies
View Related
Dec 17, 2007
I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column.
If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),1,0).......
View 11 Replies
View Related
Feb 18, 2014
I want to display the cell content after it has verified two things: 1. It pertains to the running month 2. It has maximum serial number The file enclosed is self explanatory. The formula would be dragged as every day data is likely to be added. I have written a formula, but I get error.
View 4 Replies
View Related
Jan 11, 2010
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.
Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)
Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM
I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.
=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")
Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View 14 Replies
View Related
Sep 4, 2007
I have a macro that's supposed to update the value in column 1 when two conditions are met. Unfortunately it doesn't update the column at all:
Sheets("All_Records").Select
Dim item, rownum, maxrownum As Integer
Application. ScreenUpdating = False
maxrownum = Range("A2000").End(xlUp).Row
For rownum = 2 To maxrownum
If (Cells(rownum, 1).Value = "=") & (Cells(rownum, 29).Value = "OPEN") Then
With Cells(rownum, 1)
.Value = "APPROVED TRIAL"
End With
End If
Next rownum
End Sub
I took the code from another spreadsheet I have where it works fine. But there only one condition has to be met before the field is updated.
View 4 Replies
View Related
Jan 22, 2010
I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))
Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))
But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!
So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........
View 4 Replies
View Related
Feb 22, 2010
The Table : Column R represents a list of services
Column L is the full price
Column N is the discounted price ( my example reflects no discount in this case)
Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........
View 9 Replies
View Related
Mar 2, 2006
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
View 9 Replies
View Related
